SELECT INITCAP(TRIM(TO_CHAR(dat, 'month'))) || ', ' || TO_CHAR(SYSDATE, 'yyyy') MONTH,
MAX(DECODE(TO_CHAR(dat, 'd'), 2, TO_CHAR(dat, 'dd'))) mon,
MAX(DECODE(TO_CHAR(dat, 'd'), 3, TO_CHAR(dat, 'dd'))) tue,
MAX(DECODE(TO_CHAR(dat, 'd'), 4, TO_CHAR(dat, 'dd'))) wed,
MAX(DECODE(TO_CHAR(dat, 'd'), 5, TO_CHAR(dat, 'dd'))) thu,
MAX(DECODE(TO_CHAR(dat, 'd'), 6, TO_CHAR(dat, 'dd'))) fri,
MAX(DECODE(TO_CHAR(dat, 'd'), 7, TO_CHAR(dat, 'dd'))) sat,
MAX(DECODE(TO_CHAR(dat, 'd'), 1, TO_CHAR(dat, 'dd'))) sun
FROM
(SELECT TRUNC(SYSDATE, 'y') + ROWNUM -1 dat,
TO_CHAR(TRUNC(SYSDATE, 'y') + ROWNUM -1, 'iw') woy
FROM user_objects
WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) -TRUNC(SYSDATE, 'y')))
GROUP BY TO_CHAR(dat, 'month'),
woy,
TO_CHAR(dat, 'mm')
ORDER BY TO_CHAR(dat, 'mm'),8;
MAX(DECODE(TO_CHAR(dat, 'd'), 2, TO_CHAR(dat, 'dd'))) mon,
MAX(DECODE(TO_CHAR(dat, 'd'), 3, TO_CHAR(dat, 'dd'))) tue,
MAX(DECODE(TO_CHAR(dat, 'd'), 4, TO_CHAR(dat, 'dd'))) wed,
MAX(DECODE(TO_CHAR(dat, 'd'), 5, TO_CHAR(dat, 'dd'))) thu,
MAX(DECODE(TO_CHAR(dat, 'd'), 6, TO_CHAR(dat, 'dd'))) fri,
MAX(DECODE(TO_CHAR(dat, 'd'), 7, TO_CHAR(dat, 'dd'))) sat,
MAX(DECODE(TO_CHAR(dat, 'd'), 1, TO_CHAR(dat, 'dd'))) sun
FROM
(SELECT TRUNC(SYSDATE, 'y') + ROWNUM -1 dat,
TO_CHAR(TRUNC(SYSDATE, 'y') + ROWNUM -1, 'iw') woy
FROM user_objects
WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) -TRUNC(SYSDATE, 'y')))
GROUP BY TO_CHAR(dat, 'month'),
woy,
TO_CHAR(dat, 'mm')
ORDER BY TO_CHAR(dat, 'mm'),8;
No comments:
Post a Comment