select the_week,
sunday,
saturday,
to_char(sunday, 'yyyyMMdd') || '-' || to_char(saturday, 'yyyyMMdd') week_desc
from (select sunday.the_week,
decode(sign(sunday.the_day - saturday.the_day),
-1,
sunday.the_day,
sunday.the_day - 7) sunday,
saturday.the_day saturday
from (select decode(to_char(wwm, 'yyyy/mm/dd'),
'2014/01/01',
'1',
to_char(wwm, 'WW') + 1) the_week,
to_char(wwm, 'D') the_daynum,
wwm the_day
from (select trunc(to_date('2014-01-01', 'yyyy-mm-dd'), 'MM') +
rownum - 1 as wwm
from user_objects
where rownum < 366)
where to_char(wwm, 'D') = 6
or to_char(wwm, 'yyyy/mm/dd') = '2014/01/01') sunday,
(select to_char(wwm, 'WW') + 0 the_week,
to_char(wwm, 'D') the_daynum,
wwm the_day
from (select trunc(to_date('2014-01-01', 'yyyy-mm-dd'), 'MM') +
rownum - 1 as wwm
from user_objects
where rownum < 366)
where to_char(wwm, 'D') = 5
or to_char(wwm, 'yyyy/mm/dd') = '2014/12/31') saturday
where sunday.the_week = saturday.the_week)
sunday,
saturday,
to_char(sunday, 'yyyyMMdd') || '-' || to_char(saturday, 'yyyyMMdd') week_desc
from (select sunday.the_week,
decode(sign(sunday.the_day - saturday.the_day),
-1,
sunday.the_day,
sunday.the_day - 7) sunday,
saturday.the_day saturday
from (select decode(to_char(wwm, 'yyyy/mm/dd'),
'2014/01/01',
'1',
to_char(wwm, 'WW') + 1) the_week,
to_char(wwm, 'D') the_daynum,
wwm the_day
from (select trunc(to_date('2014-01-01', 'yyyy-mm-dd'), 'MM') +
rownum - 1 as wwm
from user_objects
where rownum < 366)
where to_char(wwm, 'D') = 6
or to_char(wwm, 'yyyy/mm/dd') = '2014/01/01') sunday,
(select to_char(wwm, 'WW') + 0 the_week,
to_char(wwm, 'D') the_daynum,
wwm the_day
from (select trunc(to_date('2014-01-01', 'yyyy-mm-dd'), 'MM') +
rownum - 1 as wwm
from user_objects
where rownum < 366)
where to_char(wwm, 'D') = 5
or to_char(wwm, 'yyyy/mm/dd') = '2014/12/31') saturday
where sunday.the_week = saturday.the_week)