Week Data Setup
-- 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
;

Week Data Analysis

Date Data Analysis

Calendar Data Setup

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 14th, 2014

  280