-- Compute last Monday of a month, etc. such as Memorial Day holiday -- in USA - last Monday of May. -- Input to query -- yyyymm = 201405 -- day = MON WITH q_base_dt AS ( SELECT TO_DATE(&yyyymm,'yyyymm') ref_date FROM DUAL ), q_dates AS (SELECT q_base_dt.ref_date+level-1 cur_date, TO_CHAR(q_base_dt.ref_date+level-1,'DY') wk_day, ROW_NUMER() OVER( PARTITION BY TO_CHAR(q_base_dt.ref_date+level-1,'DY') ORDER BY q_base_dt.ref_date+level-1 ) row_num FROM q_base_dt, DUAL CONNECT BY level <= TO_CHAR(LAST_DAY(q_base_dt.ref_date),'DD') ), q_max AS (SELECT wk_day, MAX(cur_date) last_date, MAX(row_num) row_num FROM q_dates GROUP BY wk_day ) SELECT qdt.* FROM q_max, q_dates qdt WHERE q_max.row_num = qdt.row_num AND q_max.last_date = qdt.cur_date AND qdt.wk_day = UPPER('&day') ;
22117