Wednesday 28 March 2012

Create a Calendar listing in pure Oracle SQL

Just fiddling around...create a calendar for the next 1000 days starting with the 1st day of the current year. The calendar is the standard format putting Sundays in the left-most column a la :

2012 APR SUN MON TUE WED THU FRI SAT
2012 APR 1 2 3 4 5 6 7
2012 APR 8 9 10 11 12 13 14
2012 APR 15 16 17 18 19 20 21
2012 APR 22 23 24 25 26 27 28
2012 APR 29 30
2012 AUG SUN MON TUE WED THU FRI SAT
2012 AUG 1 2 3 4
2012 AUG 5 6 7 8 9 10 11
2012 AUG 12 13 14 15 16 17 18
2012 AUG 19 20 21 22 23 24 25
2012 AUG 26 27 28 29 30 31
2012 DEC SUN MON TUE WED THU FRI SAT
2012 DEC 1
2012 DEC 2 3 4 5 6 7 8
2012 DEC 9 10 11 12 13 14 15
2012 DEC 16 17 18 19 20 21 22
2012 DEC 23 24 25 26 27 28 29
2012 DEC 30 31




/*generate a calendar in Oracle using pure SQL
-- Andrew Cave andrew.cave.blogging at gmail-- 29/3/2012*/
with x as (select -- generate the days
  trunc(trunc(sysdate,'YEAR')+level-1) date_ -- add days to 1st day of current year
  from dual connect by level <= 1000 -- number of days to go forward
) 
select yr,monthname,SUN,MON,TUE,WED,THU,FRI,SAT from 
(
select yr
,monthname,MON_NUM,row_
,SUN,MON,TUE,WED,THU,FRI,SAT
from (
select -- display the dates
yr
,monthname,MON_NUM,row_
, to_char(max(decode(dayname,'SUN',dom,null))) SUN
, to_char(max(decode(dayname,'MON',dom,null))) MON
, to_char(max(decode(dayname,'TUE',dom,null))) TUE
, to_char(max(decode(dayname,'WED',dom,null))) WED
, to_char(max(decode(dayname,'THU',dom,null))) THU
, to_char(max(decode(dayname,'FRI',dom,null))) FRI
, to_char(max(decode(dayname,'SAT',dom,null))) SAT
from (
select 
yr
,mon monthname
,dayname
,dom
,MON_NUM
,trunc((dom+ fdom-1)/7)-trunc(fdom/7)  row_ -- what week of the month
,fdom 
from 
  (
  select date_ -- get date values for calculations and display
    , to_char(date_,'DY') dayname
    , to_char(date_,'YYYY') yr  
    , to_char(date_,'MON') MON
    , to_char(date_,'MM') MON_NUM
    , to_number(to_char(date_,'DD')) dom
    , to_number(to_char(trunc(date_,'MM'),'D')) fdom -- day value of 1st day of month
  from x
  ) x1
) x2 group by yr,monthname,row_,MON_NUM
) x3 
union
select distinct -- this select does the day name column headers for the calendar
  to_char(date_,'YYYY') yr
  , to_char(date_,'MON') MONthname
  , to_char(date_,'MM') MON_NUM
  , -1 row_
  ,'SUN','MON','TUE','WED','THU','FRI','SAT'
from x
) x4  order by yr, MON_NUM, row_

No comments:

Post a Comment