관리 메뉴

행이네

Oracle - 월 달력 SQL 본문

plming/DB

Oracle - 월 달력 SQL

행이™ 2015. 12. 3. 11:07

기본 명령들만 사용해서

년,월 (예:201512) 을 입력받고

해당 년월의 달력 형태로 조회하는 SQL

 

 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

 

 

'plming > DB' 카테고리의 다른 글

[Oracle] WITH(Temp Table) 2개 사용하기?  (0) 2016.04.07
[Oracle] 조회 결과 Row를 하나의 Column으로 표시하는 방법  (0) 2016.04.07
Oracle - 월 달력 SQL  (0) 2015.12.03
MySQL - 한글사용  (0) 2015.09.02
Oracle VS MySQL  (0) 2015.09.02
MySQL - ALTER  (0) 2015.09.02
0 Comments
댓글쓰기 폼