[Oracle] 달력 쿼리 셈플

달력 쿼리 셈플

WITH CALENDAR AS
(
    SELECT CEIL((T1.RNUM + 7 - TO_NUMBER(TO_CHAR(TO_DATE('201606' || LPAD(TO_CHAR(T1.RNUM), 2, '0'), 'YYYYMMDD'), 'D'))) / 7) WOM,
           TO_CHAR(TO_DATE('201606' || LPAD(TO_CHAR(T1.RNUM), 2, '0'), 'YYYYMMDD'), 'D') DOW,
           '201606' || LPAD(TO_CHAR(T1.RNUM), 2, '0') CAL_YMD,
           T1.RNUM DOM 
      FROM (SELECT ROWNUM RNUM FROM ALL_OBJECTS WHERE ROWNUM <= 31) T1
     WHERE TO_CHAR(LAST_DAY(TO_DATE('201606' || '01', 'YYYYMMDD')), 'DD') >= LTRIM(TO_CHAR(T1.RNUM, '00'))
)
SELECT TO_CHAR(MAX(DECODE(T1.DOW, '1', T1.DOM))) SUN_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '2', T1.DOM))) MON_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '3', T1.DOM))) TUE_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '4', T1.DOM))) WED_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '5', T1.DOM))) THU_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '6', T1.DOM))) FRI_EMP_NM,
       TO_CHAR(MAX(DECODE(T1.DOW, '7', T1.DOM))) SAT_EMP_NM,
       T1.WOM || '주' AS WEEK,
       1 SORT_ORDER,
       MAX(DECODE(T1.DOW, '1', LPAD(TO_CHAR(T1.DOM), 2, '0'))) SUN,
       MAX(DECODE(T1.DOW, '2', LPAD(TO_CHAR(T1.DOM), 2, '0'))) MON,
       MAX(DECODE(T1.DOW, '3', LPAD(TO_CHAR(T1.DOM), 2, '0'))) TUE,
       MAX(DECODE(T1.DOW, '4', LPAD(TO_CHAR(T1.DOM), 2, '0'))) WED,
       MAX(DECODE(T1.DOW, '5', LPAD(TO_CHAR(T1.DOM), 2, '0'))) THU,
       MAX(DECODE(T1.DOW, '6', LPAD(TO_CHAR(T1.DOM), 2, '0'))) FRI,
       MAX(DECODE(T1.DOW, '7', LPAD(TO_CHAR(T1.DOM), 2, '0'))) SAT
  FROM CALENDAR T1
 GROUP BY T1.WOM
 UNION ALL
SELECT MAX(DECODE(T1.DOW, '1', '')) AS SUN_EMP_NM,
       MAX(DECODE(T1.DOW, '2', '')) AS MON_EMP_NM,
       MAX(DECODE(T1.DOW, '3', '')) AS TUE_EMP_NM,
       MAX(DECODE(T1.DOW, '4', '')) AS WED_EMP_NM,
       MAX(DECODE(T1.DOW, '5', '')) AS THU_EMP_NM,
       MAX(DECODE(T1.DOW, '6', '')) AS FRI_EMP_NM,
       MAX(DECODE(T1.DOW, '7', '')) AS SAT_EMP_NM,
       T1.WOM || '주' AS WEEK,
       2 SORT_ORDER,
       MAX(DECODE(T1.DOW, '1', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) SUN,
       MAX(DECODE(T1.DOW, '2', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) MON,
       MAX(DECODE(T1.DOW, '3', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) TUE,
       MAX(DECODE(T1.DOW, '4', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) WED,
       MAX(DECODE(T1.DOW, '5', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) THU,
       MAX(DECODE(T1.DOW, '6', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) FRI,
       MAX(DECODE(T1.DOW, '7', '201606' || LPAD(TO_CHAR(T1.DOM), 2, '0'))) SAT
  FROM CALENDAR T1
 GROUP BY T1.WOM
 ORDER BY WEEK, SORT_ORDER