-- Most companies use calendars that start on Saturday, Sunday, Monday. The -- weekend date is based on the week start date. This query can be used to -- create dates for a calendar such as a ERP GL Calendar or any company -- specific fiscal calendar. WITH q_dates AS (SELECT level wk_day, TO_CHAR(TO_DATE(&yyyymmdd,'yyyymmdd')+level-1,'mm/dd/yyyy') start_date, DECODE(mod(level,7),0,level/7,trunc(level/7)+1) wk_No FROM DUAL CONNECT BY level <= 7*&i_wk ) SELECT wk_day, start_date week_date, TO_CHAR(TO_DATE(start_date,'mm/dd/yyyy')+7*wk_no-wk_day,'mm/dd/yyyy') eow_date, wk_no, DECODE(GREATEST(wk_no,14),wk_no, DECODE(GREATEST(wk_no,27),wk_no, DECODE(GREATEST(wk_no,40),wk_no,4,3),2),1) qtr_val FROM q_dates ; -- The input to the above query is the begining of the week - First -- Saturday of the year (01/03/2015) and the number of weeks is setup to -- be 52. The week ending dates are Friday dates corresponding to the -- prior Saturday dates as computed by the query. Partial output is -- shown below. -- To create calendar dates for multiple years, PL/SQL code would be a -- better option with the use of FOR or WHILE LOOP syntax with the -- ability to reset variables at completion of a year.
52868