|
SELECT WEEK_NUM
, MAX(DECODE(DAY_NUM, 1, MONTH_DAY)) AS DAY1
, MAX(DECODE(DAY_NUM, 2, MONTH_DAY)) AS DAY2
, MAX(DECODE(DAY_NUM, 3, MONTH_DAY)) AS DAY3
, MAX(DECODE(DAY_NUM, 4, MONTH_DAY)) AS DAY4
, MAX(DECODE(DAY_NUM, 5, MONTH_DAY)) AS DAY5
, MAX(DECODE(DAY_NUM, 6, MONTH_DAY)) AS DAY6
, MAX(DECODE(DAY_NUM, 7, MONTH_DAY)) AS DAY7
FROM ( SELECT DECODE( DAY_NUM, 1, WEEK_NUM+1, WEEK_NUM ) AS WEEK_NUM
, DAY_NUM
, MONTH_DAY
FROM ( SELECT DECODE(IS_JAN, 'Y', CASE WHEN TO_CHAR(MONTH_DAY, 'DD') <= '06'
AND TO_CHAR(MONTH_DAY, 'IW') > '50'
THEN '00'
ELSE TO_CHAR(MONTH_DAY, 'IW')
END
, TO_CHAR(MONTH_DAY, 'IW')) AS WEEK_NUM
, TO_CHAR(MONTH_DAY, 'D') AS DAY_NUM
, RNUM AS MONTH_DAY
FROM ( SELECT a.START_DT + (b.RNUM-1) AS MONTH_DAY
, a.IS_JAN
, b.RNUM
FROM ( SELECT TO_DATE('201512'||'01', 'YYYYMMDD') AS START_DT
, DECODE(SUBSTR('201512',5,2), '01', 'Y') AS IS_JAN
FROM DUAL
) a
, ( SELECT LEVEL AS RNUM
FROM DUAL
CONNECT BY LEVEL <= ( SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('201512'||'01', 'YYYYMMDD')),'DD'))
FROM DUAL )
) b
)
)
)
GROUP BY WEEK_NUM
ORDER BY WEEK_NUM |