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