-- The query below uses Oracle Row_Number() analytic function -- to count days of the week starting with Sunday. For just a -- single month data analysis/creation, correction will be -- needed for those that do not start with sunday as 1, as -- October 2013. -- Sunday => 1 -- Monday => 2 -- ... and so on -- Saturday => 7 -- The MAX(value) OVER(PARTITION BY .. ORDER BY ..) can be -- used to identify those that have a value of Saturday of -- less than 7 -- The week_num => CEIL(level/7) splits the days into 7 day -- week in a month (31/7, 30/7, 28/7 or 29/7). It can be -- used to create week counter (1 to 7) with each 7 day week. WITH q_date AS (SELECT &yyyy ref_yr FROM DUAL), q_days AS ( SELECT TO_DATE(ref_yr,'yyyy') ref_date, LAST_DAY(TO_DATE(ref_yr,'yyyy'))+1-TO_DATE(ref_yr,'yyyy') days FROM q_date), q_day_cnt AS ( SELECT level day_idx, ref_date+level-1 cal_date, CEIL(level/7) week_Num, ROW_NUMBER() OVER (PARTITION BY CEIL(level/7) ORDER BY level) week_day, CAST(TO_CHAR(ref_date+level-1,'Day') AS VARCHAR2(15)) wk_day, NEXT_DAY(ref_date+level-1,'SUN') wk_begin_day, ROW_NUMBER() OVER (PARTITION BY NEXT_DAY(ref_date+level-1,'SUN') ORDER BY level) sunday_wk, to_char(ref_date+level-1,'mm/dd/yyyy') cal_date_str FROM q_days CONNECT BY level <= days) SELECT day_idx, week_num, week_day "7Day Week", TO_CHAR(wk_begin_day,'mm/dd/yyyy') sunday_date, CAST(TO_CHAR(cal_date,'DY') AS VARCHAR2(3)) "DAY", sunday_wk, cal_date_str, MAX(sunday_wk) OVER (PARTITION BY wk_begin_day) max_day FROM q_day_cnt ORDER BY day_idx; -- Same values can be obtained by using Oracle RANK() or -- Dense_Rank() analytic function WITH q_date AS (SELECT &yyyy ref_yr FROM DUAL), q_days AS ( SELECT TO_DATE(ref_yr,'yyyy') ref_date, LAST_DAY(TO_DATE(ref_yr,'yyyy'))+1-TO_DATE(ref_yr,'yyyy') days FROM q_date) SELECT level day_idx, CEIL(level/7) week_Num, RANK() OVER (PARTITION BY CEIL(level/7) ORDER BY level) week_day, CAST(TO_CHAR(ref_date+level-1,'Day') AS VARCHAR2(15)) wk_day, NEXT_DAY(ref_date+level-1,'SUN') wk_begin_day, RANK() OVER (PARTITION BY NEXT_DAY(ref_date+level-1,'SUN') ORDER BY level) sunday_wk, to_char(ref_date+level-1,'mm/dd/yyyy') cal_date_str FROM q_days CONNECT BY level <= days; => Row_Number and Rank Output
52885