SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu",
"We", "Th", "Fr", "Sa"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
CASE
WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
AND TO_CHAR (dt + 1, 'iw') = '01'
THEN '53'
WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
AND TO_CHAR (dt + 1, 'iw') = '53'
THEN '.5'
ELSE TO_CHAR (dt + 1, 'iw')
END week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Su",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mo",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "We",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Th",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fr",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sa"
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_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)
"We", "Th", "Fr", "Sa"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
CASE
WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
AND TO_CHAR (dt + 1, 'iw') = '01'
THEN '53'
WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
AND TO_CHAR (dt + 1, 'iw') = '53'
THEN '.5'
ELSE TO_CHAR (dt + 1, 'iw')
END week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Su",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mo",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "We",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Th",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fr",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sa"
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_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)
No comments:
Post a Comment