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