-- Create fiscal year based on any starting day for a week (using Monday in this -- query). SELECT cur_date+level-1 fiscal_date, TO_CHAR(cur_date+level-1,'DY') week_day, CEIL(level/7) week_no, yr_days FROM (SELECT MIN(q_dates.cur_date) cur_date, q_dates.yr_days FROM (SELECT qy.first_day+level-1 cur_date, TO_CHAR(qy.first_day+level-1,'DY') wk_day, LAST_DAY(ADD_MONTHS(qy.first_day,11)) last_date, LAST_DAY(ADD_MONTHS(qy.first_day,11))+1-qy.first_day yr_days FROM (SELECT TRUNC(TO_DATE(&yyyy,'yyyy'),'yyyy') first_day FROM DUAL) qy CONNECT BY level <=8 ) q_dates WHERE q_dates.wk_day = 'MON' GROUP BY q_dates.yr_days ) CONNECT BY level <= yr_days ;
55616