Wednesday 20 July 2011

Query to Print Calendar for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH,
"Sun", "Mon","Tue", "Wed", "Thu", "Fri", "Sat"

FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,

MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sun",

MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Mon",

MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Tue",

MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Wed",

MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Thu",

MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Fri",

MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sat"

FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt

FROM all_objects

WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y'))
GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))

ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

No comments:

Post a Comment