-- In business situations, expense submission for a month should completed -- by first 10 business days of the following month, which in many cases -- excludes weekend days and holidays (if any) in the month. Using Oracle -- analytic function, the tenth day can be computed as shown. -- The holiday exclusion is specific to company/organization and has to be -- computed using their calendar. The computed holidays (third Monday -- etc.) can also be computed using Oracle analytic function as shown in -- the -- Annual Calendar Creation Query -- In the query below, fixed holiday values are used (01/20, 02/17). WITH q_start AS ( SELECT TO_DATE(20140101,'yyyymmdd') ref_date FROM DUAL ), q_days AS ( SELECT level mth_day, TO_CHAR(ref_date+level-1,'yyyymm') month_idx, TO_CHAR(ref_date+level-1,'DY') week_day, ref_date+level-1 cal_date, DECODE(TO_CHAR(ref_date+level-1,'mmdd'), '0101','Y', '0120','Y','0217','Y','N') holiday_yn, DECODE(TO_CHAR(ref_date+level-1,'DY'), 'SAT','N','SUN','N','Y') bus_day FROM q_start CONNECT BY level < 90) SELECT mth_day, month_idx, TO_CHAR(cal_date,'mm/dd/yyyy') cal_date, week_day, bus_day, holiday_yn, SUM(DECODE(bus_day,'Y', DECODE(holiday_yn,'Y',0,1),0)) OVER (PARTITION BY month_idx ORDER BY mth_day) bus_days FROM q_days ; => Above Query Output Showing Business Day Count WITH q_start AS ( SELECT TO_DATE(20140101,'yyyymmdd') ref_date FROM DUAL ), q_days AS ( SELECT level mth_day, TO_CHAR(ref_date+level-1,'yyyymm') month_idx, TO_CHAR(ref_date+level-1,'DY') week_day, ref_date+level-1 cal_date, DECODE(TO_CHAR(ref_date+level-1,'mmdd'), '0101','Y', '0120','Y','0217','Y','N') holiday_yn, DECODE(TO_CHAR(ref_date+level-1,'DY'), 'SAT','N','SUN','N','Y') bus_day FROM q_start CONNECT BY level < 90), q_bdays AS ( SELECT month_idx, mth_day, cal_date, week_day, bus_day, holiday_yn, SUM(DECODE(bus_day,'Y', DECODE(holiday_yn,'Y',0,1),0)) OVER (PARTITION BY month_idx ORDER BY mth_day) bus_days FROM q_days) SELECT month_idx, bus_days due_on_day, TO_CHAR(MIN(cal_date),'DY') week_day, MIN(mth_day) mth_day, TO_CHAR(MIN(cal_date),'mm/dd/yyyy') cal_date FROM q_bdays WHERE bus_days = 10 GROUP BY month_idx, bus_days ORDER BY month_idx ; => Above Query Output Showing First Tenth Business Day MONTH_ DUE_ON_DAY WEEK_DAY MTH_DAY CAL_DATE ------ ---------- ------------ ---------- ---------- 201401 10 WED 15 01/15/2014 201402 10 FRI 45 02/14/2014 201403 10 FRI 73 03/14/2014
52888