SELECT
LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH
,TO_NUMBER (week) week
, "Sun"
, "Mon"
, "Tue"
, "Wed"
, "Thu"
, "Fri"
, "Sat"
FROM
(SELECT
TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 1, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Sun",
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 2, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Mon",
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 3, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Tue",
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 4, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Wed",
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 5, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Thu",
TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 6, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Fri",
TO_NUMBER(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