꿈꾸는 개발자, DBA 커뮤니티 구루비

시작/종료일자를 년도별로 조회하기

by 엘시아 시작일자 종료일자 GROUP BY 년도별 [2016.08.10 18:05:37]

안녕하세요

WITH T AS
(
SELECT 'A' AS NO ,'20130506' AS STA_YMD, '20160805' AS END_YMD   FROM DUAL
  UNION ALL
SELECT 'B','20100120' , '20110203'    FROM DUAL
)

NO별로 날짜를 자고싶은데.. 어렵습니다 ㅠ..

이거를 아래처럼 년도별로 자르는 쿼리를 만들고싶습니다.

NO sta_ymd end_ymd
A 20130506 20131231

A 20140101 20141231

A 20150101 20151231

A 20160101 20160805

B 20100120 20101231

B 20110101 20110203

이렇게 가능할까요 ㅠㅠ? 어렵네요;;

by 신이만든짝퉁 [2016.08.10 18:50:13]

WITH T AS
     (SELECT 'A' AS NO, '20130506' BIG_YMD, '20160805' END_YMD
        FROM DUAL
      UNION ALL
      SELECT 'B' AS NO, '20100120' BIG_YMD, '20110203' END_YMD
        FROM DUAL)
SELECT   *
    FROM (SELECT NO
               , BYMD
               , EYMD
               , T2.RN
               , CASE
                     WHEN RN = 0 THEN BYMD
                     ELSE ADD_MONTHS(TRUNC(BYMD, 'YEAR'), 12 * RN)
                 END BYMD2
               , CASE
                     WHEN RN = PYEAR THEN EYMD
                     ELSE ADD_MONTHS(TRUNC(BYMD, 'YEAR'), 12 *(RN + 1)) - 1
                 END EYMD2
            FROM (SELECT NO
                       , TO_DATE(BIG_YMD, 'YYYYMMDD') BYMD
                       , TO_DATE(END_YMD, 'YYYYMMDD') EYMD
                       , FLOOR(MONTHS_BETWEEN(TO_DATE(END_YMD, 'YYYYMMDD'), TO_DATE(BIG_YMD, 'YYYYMMDD')) / 12) PYEAR
                    FROM T) T1
               , (SELECT     LEVEL - 1 RN
                        FROM DUAL
                  CONNECT BY LEVEL <= 100) T2)  -- 100년 이상 차이나는 경우는 없다고 가정함
   WHERE BYMD2 >= BYMD AND EYMD2 <= EYMD
ORDER BY NO ASC, BYMD2 ASC;

이렇게 해보세요

by 엘시아 [2016.08.11 08:50:47]

감사합니다^^

by jkson [2016.08.11 08:15:50]

WITH T AS
(
SELECT 'A' AS NO ,'20130506' AS STA_YMD, '20160805' AS END_YMD FROM DUAL
UNION ALL
SELECT 'B','20100120' , '20110203' FROM DUAL
)
SELECT NO
     , GREATEST(STA_YMD,TO_CHAR(TO_NUMBER(SUBSTR(STA_YMD,1,4)) + LV - 1)||'0101')  STA_YMD
     , LEAST(END_YMD,TO_CHAR(TO_NUMBER(SUBSTR(STA_YMD,1,4)) + LV - 1)||'1231') END_YMD
  FROM T
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100)
WHERE TO_NUMBER(SUBSTR(END_YMD,1,4)) - TO_NUMBER(SUBSTR(STA_YMD,1,4)) + 1 >= LV
ORDER BY 1,2

by 엘시아 [2016.08.11 08:51:07]

감사합니다 ㅎㅎ

by 탱 [2016.08.11 11:53:19]

WITH T AS (
SELECT 'A' AS NO, '20130506' AS STA_YMD, '20160805' AS END_YMD FROM DUAL
UNION ALL
SELECT 'B', '20100120', '20110203' FROM DUAL
)
SELECT S.NO NO
     , CASE WHEN STA_YMD >= SUBSTR(STA_YMD,1,4)+D.LV-1||'0101' AND STA_YMD < SUBSTR(STA_YMD,1,4)+D.LV||'0101'
            THEN STA_YMD
            ELSE SUBSTR(STA_YMD,1,4)+D.LV-1||'0101'
       END STA_YMD
     , CASE WHEN END_YMD > SUBSTR(STA_YMD,1,4)+D.LV-2||'1231' AND END_YMD <= SUBSTR(STA_YMD,1,4)+D.LV-1||'1231'
            THEN END_YMD
            ELSE SUBSTR(STA_YMD,1,4)+D.LV-1||'1231'
       END END_YMD
FROM T S ,(SELECT DISTINCT NO, LEVEL LV
           FROM T S
           CONNECT BY LEVEL <= (SUBSTR(S.END_YMD,1,4)-SUBSTR(S.STA_YMD,1,4)+1)) D
WHERE 1=1
AND S.NO = D.NO
ORDER BY NO, LV;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변