[Oracle] 년월을 입력받아 달력 생성 및 출력하는 쿼리 만들기

시행착오가 있었지만 년월을 입력받아 달력 생성 및 출력하는 쿼리 만들기에 성공하였다.

CASE문 대신 DECODE 함수를 쓰면 더 간단하게 출력 가능하다!

 

첫 번째 시도 - 실패

문제점 1. &calendar  선언을 중복으로 하였음 (3번)
문제점 2. 199901를 입력시 정렬이 깨짐  즉, 주차 계산을 제대로 못하였음

SELECT Min(CASE
             WHEN To_char(dates, 'D') = 1 THEN To_char(dates, 'DD')
           END) 일,
       Min(CASE
             WHEN To_char(dates, 'D') = 2 THEN To_char(dates, 'DD')
           END) 월,
       Min(CASE
             WHEN To_char(dates, 'D') = 3 THEN To_char(dates, 'DD')
           END) 화,
       Min(CASE
             WHEN To_char(dates, 'D') = 4 THEN To_char(dates, 'DD')
           END) 수,
       Min(CASE
             WHEN To_char(dates, 'D') = 5 THEN To_char(dates, 'DD')
           END) 목,
       Min(CASE
             WHEN To_char(dates, 'D') = 6 THEN To_char(dates, 'DD')
           END) 금,
       Min(CASE
             WHEN To_char(dates, 'D') = 7 THEN To_char(dates, 'DD')
           END) 토
FROM   (SELECT ( To_date(&calendar, 'YYYYMM') + LEVEL - 1 ) dates,
               LEVEL
        FROM   dual
        CONNECT BY ( To_date(&calendar, 'YYYYMM') + LEVEL - 1 ) <= Last_day(
                   To_date(&calendar, 'YYYYMM')))
GROUP  BY ( CASE
              WHEN To_char(dates, 'D') = 1 THEN To_char(dates, 'IW') + 1
              ELSE To_char(dates, 'IW') + 0
            END )
ORDER  BY ( CASE
              WHEN To_char(dates, 'D') = 1 THEN To_char(dates, 'IW') + 1
              ELSE To_char(dates, 'IW') + 0
            END );

 

이상한 달력

 

두 번째 시도 - 성공 (일요일부터 출력)

문제점 해결 1. 변수를 제일 처음 select 하는 절에 한번만 선언
문제점 해결 2. 
1) TRUNC (SYSDATE + LEVEL - 1, 'd') : TRUNC와 'd'옵션을 사용하여 해당 주의 요일로 초기화 (일요일로 시작) 
2) TRUNC (SYSDATE + LEVEL - 1, 'y') : TRUNC와 'y'옵션을 사용하여 해당 주의 월,일 초기화 (01-01로 시작) 
3) TRUNC(TRUNC (SYSDATE + LEVEL - 1, 'y', 'd')) 
4) 계산법 : (주간의 첫 날짜(일요일) - 1월1일 주간의 첫 날짜(일요일)) / 7 + 1

근데 여기서 7 + 1 안하고 7해도 답이 나오는데..어떤 차이인지 아직 발견하지 못하였다..

 

+)참고 : 날짜와 관련한 TRUNC 활용 (TRUNC(sysdate, 'd'))

▶ 'w' : 매월1일 요일을 기준한 주초일자.
▶ 'ww' : 매년 1월1일 요일을 기준한 주초일자.
▶ 'iw' : 주초 월요일일자.
▶ 'd' / 'dy' / 'day' : 주초 일요일 일자.
▶ 'month' : 기준달의 1일자.
▶ 'year' : 기준년의 1월 1일자.

 

SELECT Min(CASE
             WHEN To_char(dates, 'D') = 1 THEN To_char(dates, 'DD')
           END) 일,
       Min(CASE
             WHEN To_char(dates, 'D') = 2 THEN To_char(dates, 'DD')
           END) 월,
       Min(CASE
             WHEN To_char(dates, 'D') = 3 THEN To_char(dates, 'DD')
           END) 화,
       Min(CASE
             WHEN To_char(dates, 'D') = 4 THEN To_char(dates, 'DD')
           END) 수,
       Min(CASE
             WHEN To_char(dates, 'D') = 5 THEN To_char(dates, 'DD')
           END) 목,
       Min(CASE
             WHEN To_char(dates, 'D') = 6 THEN To_char(dates, 'DD')
           END) 금,
       Min(CASE
             WHEN To_char(dates, 'D') = 7 THEN To_char(dates, 'DD')
           END) 토
FROM   (SELECT dates,
               week
        FROM   (SELECT base_mon + LEVEL - 1 dates,
                       ( Trunc(base_mon + LEVEL - 1, 'd') - Trunc(
                         Trunc(base_mon + LEVEL - 1, 'y'), 'd' ) ) / 7 WEEK
                -- 주 계산 후 정렬에 사용
                FROM   (SELECT To_date('&calendar', 'yyyymm') base_mon
                        -- 변수 선언 1번으로 수정
                        FROM   dual)
                CONNECT BY LEVEL <= To_char(Last_day(base_mon), 'dd')))
GROUP  BY week
ORDER  BY week;

일요일부터 출력성공

 

세 번째 시도 - 성공 (월요일부터 출력)

월요일부터 출력시 TO_CHAR( date [,'fmt' [,'nlsparam']]) 오라클 설정변경

SELECT Min(CASE
             WHEN To_char(dates, 'D') = 2 THEN To_char(dates, 'DD')
           END) 월,
       Min(CASE
             WHEN To_char(dates, 'D') = 3 THEN To_char(dates, 'DD')
           END) 화,
       Min(CASE
             WHEN To_char(dates, 'D') = 4 THEN To_char(dates, 'DD')
           END) 수,
       Min(CASE
             WHEN To_char(dates, 'D') = 5 THEN To_char(dates, 'DD')
           END) 목,
       Min(CASE
             WHEN To_char(dates, 'D') = 6 THEN To_char(dates, 'DD')
           END) 금,
       Min(CASE
             WHEN To_char(dates, 'D') = 7 THEN To_char(dates, 'DD')
           END) 토,
       Min(CASE
             WHEN To_char(dates, 'D') = 1 THEN To_char(dates, 'DD')
           END) 일
FROM   (SELECT dates,
               CASE
                 WHEN To_char(dates, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SUN'
               THEN
               --'DY'를 통해서 각 요일의 앞글자만 출력
               To_number(week) - 1
                 ELSE week
               END week
        FROM   (SELECT base_mon + LEVEL - 1  dates,
                       ( Trunc(base_mon + LEVEL - 1, 'd') - Trunc(
                         Trunc(base_mon + LEVEL - 1, 'y'), 'd'
                                                            ) ) / 7 WEEK
                FROM   (SELECT To_date('&calendar', 'yyyymm') base_mon
                        FROM   dual)
                CONNECT BY LEVEL <= To_char(Last_day(base_mon), 'dd')))
GROUP  BY week
ORDER  BY week;

월요일부터 출력 성공