Sql Puzzle - Calendar of Current Year

BREAK ON MONTH

BREAK ON MONTH SKIP PAGE

COL MONTH FORMAT A15

 with curr_year as

   (

     select

       trunc(sysdate,'year') -1 + level dt

     from dual

       connect by level <=

         add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')

   ),

   data as

   (

     select

       dt,

       to_char(dt,'d') d,

       sum(case when to_char(dt,'d') = 1 or to_char(dt,'dd') = 1 then 1 else 0 end) over (order by dt) week_no

     from

       curr_year

   )

   select

     to_char(min(dt),'Month') Month,

     max(case when d=1 then to_char(dt,'dd') end) sun,

     max(case when d=2 then to_char(dt,'dd') end) mon,

     max(case when d=3 then to_char(dt,'dd') end) tue,

     max(case when d=4 then to_char(dt,'dd') end) wed,

     max(case when d=5 then to_char(dt,'dd') end) thu,

     max(case when d=6 then to_char(dt,'dd') end) fri,

     max(case when d=7 then to_char(dt,'dd') end) sat

   from

     data

   group by week_no

   order by week_no;