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.
