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