-- 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