ID | 구분숫자 | 시작일 | 종료일 | 값 | 구분코드 |
a | 1 | 20170102 | 20180303 | 5 | A |
a | 1 | 20170102 | 20170701 | 100 | B |
a | 2 | 20170701 | 99991231 | 105 | B |
a | 1 | 20170102 | 20170615 | 13 | C |
a | 2 | 20170615 | 20171225 | 23 | C |
a | 3 | 20171225 | 99991231 | 14 | C |
b |
1 | 20180302 | 20181012 | 4 | A |
안녕하세요 db 예시는 저렇습니다.구분코드에서 날짜가 변하면 다른 계약으로 취급할려고 합니다.
원하는 형태는
ID | 시작일 | 종료일 | 구분코드A값 | 구분코드B 값 | 구분코드C값 | |
a | 20170102 | 20170615 | 5 | 100 | 13 | |
a | 20170615 | 20170701 | 5 | 100 | 23 | |
a | 20170701 | 20171225 | 5 | 105 | 23 | |
a | 20171225 | 20180803 | 5 | 105 | 14 | |
b | 20180302 | 4 | ||||
이런식으로 결과를 출력하려 합니다.. 설명이 미흡해보이지만 이해가 되신다면 답변 부탁드리겠습니다.
WITH t AS ( SELECT 'a' id, 1 no, '20170102' sdt, '20180303' edt, 5 v, 'A' cd FROM dual UNION ALL SELECT 'a', 1, '20170102', '20170701', 100, 'B' FROM dual UNION ALL SELECT 'a', 2, '20170701', '99991231', 105, 'B' FROM dual UNION ALL SELECT 'a', 1, '20170102', '20170615', 13, 'C' FROM dual UNION ALL SELECT 'a', 2, '20170615', '20171225', 23, 'C' FROM dual UNION ALL SELECT 'a', 3, '20171225', '99991231', 14, 'C' FROM dual UNION ALL SELECT 'b', 1, '20180302', '20181012', 4, 'A' FROM dual ) SELECT a.id, a.sdt, a.edt , MIN(DECODE(b.cd, 'A', b.v)) a , MIN(DECODE(b.cd, 'B', b.v)) b , MIN(DECODE(b.cd, 'C', b.v)) c FROM (SELECT id, sdt , LEAD(sdt) OVER(PARTITION BY id ORDER BY sdt) edt FROM (SELECT id, sdt, edt FROM t) UNPIVOT (sdt FOR gb IN (sdt, edt)) GROUP BY id, sdt ) a , t b WHERE a.id = b.id AND a.sdt < b.edt AND a.edt > b.sdt GROUP BY a.id, a.sdt, a.edt ORDER BY a.id, a.sdt ; -- Result -- ID SDT EDT A B C a 20170102 20170615 5 100 13 a 20170615 20170701 5 100 23 a 20170701 20171225 5 105 23 a 20171225 20180303 5 105 14 a 20180303 99991231 105 14 b 20180302 20181012 4
WITH T (ID,구분숫자,시작일,종료일,값,구분코드 ) AS ( SELECT 'a','1','20170102','20180303','5','A' FROM DUAL UNION ALL SELECT 'a','1','20170102','20170701','100','B' FROM DUAL UNION ALL SELECT 'a','2','20170701','99991231','105','B' FROM DUAL UNION ALL SELECT 'a','1','20170102','20170615','13','C' FROM DUAL UNION ALL SELECT 'a','2','20170615','20171225','23','C' FROM DUAL UNION ALL SELECT 'a','3','20171225','99991231','14','C' FROM DUAL UNION ALL SELECT 'b','1','20180302','20181012','4','A' FROM DUAL ) SELECT A.ID , 시작일 , LEAST(종료일, NVL(LEAD(시작일,1) OVER(PARTITION BY ID ORDER BY 시작일),종료일)) 종료일 , LAST_VALUE(A) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) A , LAST_VALUE(B) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) B , LAST_VALUE(C) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) C FROM( SELECT ID , 시작일 , CASE WHEN MAX(종료일) = '99991231' THEN '1' ELSE MAX(종료일) END 종료일 , MIN(CASE WHEN 구분코드 = 'A' THEN 값 END) A , MIN(CASE WHEN 구분코드 = 'B' THEN 값 END) B , MIN(CASE WHEN 구분코드 = 'C' THEN 값 END) C FROM ( SELECT ID,구분숫자,시작일,MAX(CASE WHEN 종료일 != '99991231' THEN 종료일 END ) OVER(PARTITION BY ID) 종료일,값,구분코드 FROM T ) A GROUP BY ID , 시작일 ) A -- RESULT ID 시작일 종료일 A B C - -------- -------- --- --- --- a 20170102 20170615 5 100 13 a 20170615 20170701 5 100 23 a 20170701 20171225 5 105 23 a 20171225 20180303 5 105 14 b 20180302 20181012 4