Tuesday, November 23, 2021

Fun - Query Generated Calendar

Here's a query that will generate a full year calendar given any year. Enter the 4-digit year in the first line and execute the code.

  

   WITH  year  as ( select  /*==>*/ 2022 /*<==*/ as yr from dual )
   select  tag as "Month", sun, mon, tue, wed, thu, fri, sat
     from  ( select  tag
                  ,  sum( decode(dw, '1', dm) ) sun
                  ,  sum( decode(dw, '2', dm) ) mon
                  ,  sum( decode(dw, '3', dm) ) tue
                  ,  sum( decode(dw, '4', dm) ) wed
                  ,  sum( decode(dw, '5', dm) ) thu
                  ,  sum( decode(dw, '6', dm) ) fri
                  ,  sum( decode(dw, '7', dm) ) sat
               from  ( select  to_char((yrdt-1+rownum),'MM')  as mn
                            ,  to_char((yrdt-1+rownum),'d')  as dw
                            ,  lpad(to_char((yrdt-1+rownum),'fmdd'),2)  as dm
                            ,  to_char((yrdt-1+rownum)+1,'iw')  as iw
                            ,  to_char((yrdt-1+rownum),'YYYY-MM (Mon)')  as tag
                         from  ( select  trunc(to_date( yr,'YYYY'),'YYYY') 
                                     as  yrdt  from  year )
                        where  rownum <= add_months(yrdt, 12) - yrdt
                      connect  by level <= 366 
                     )
              group  by  tag, mn, iw 
           )
    order  by  tag, sun NULLS first ;
  

Note: this code is not the product of my brain-steam. I found this algorithm somewhere on the web and have enjoyed playing with it. I apologize for not remembering where or who originally came up with it.