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);
Sample Output:-
"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);
Sample Output:-
No comments:
Post a Comment