Compute Holidays
-- Holidays that are designated as first Monday, fourth Thursday etc. can be computed as
-- shown below using the analytic function ROW_NUMBER(). The third Monday of January is
-- Martin Luther King Day in USA.  In 2014, it is January 20th 2014. 

WITH q_date AS
(SELECT  &i_yyyymm  i_yyyymm FROM  DUAL),
 q_days AS 
(SELECT
   TO_DATE(i_yyyymm,'yyyymm') bom_date,
   LAST_DAY(TO_DATE(i_yyyymm,'yyyymm'))+1-TO_DATE(i_yyyymm,'yyyymm') mth_days
 FROM q_date), 
 q_wk_days AS
(SELECT
   bom_date+level-1 mth_day,
   TO_CHAR(bom_date+level-1,'Dy') wk_day   
 FROM  q_days
 CONNECT BY level <= mth_days),
 q_holiday AS
(SELECT 
   mth_day ref_date,
   ROW_NUMBER() OVER (ORDER BY wk_day) day_cnt
 FROM  q_wk_days
 WHERE  wk_day = INITCAP('&i_3ltr_day'))     -- Mon, Tue, etc.
SELECT 
 TO_CHAR(ref_date,'mm/dd/yyyy') holiday_date
FROM q_holiday
WHERE  day_cnt = &i_cnt;
             
Duplicate Data Analysis

-- Third Monday of February is President's day and it is February 17th, 2014.  Using 
-- the same query, the first Monday of September is Labor Day in USA, which is
-- computed as September 1st, 2014. 


Output: Other Similar Holidays

N-th Business Day Query

Row_Number() and Rank Query

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2014

  356