아래 처럼 코드별로 연속일 일자로 데이터가 있을 경우 WHIT t AS ( SELECT 'A' cd, '20230101' dt from dual UNION ALL SELECT 'A' , '20230103' from dual UNION ALL SELECT 'A' , '20230104' from dual UNION ALL SELECT 'B' , '20230103' from dual UNION ALL SELECT 'A' , '20230106' from dual UNION ALL SELECT 'A' , '20230107' from dual UNION ALL SELECT 'B' , '20230106' from dual UNION ALL SELECT 'B' , '20230107' from dual )
결과값은 아래처럼 나왔으면 좋겠습니다.
코드 시작일자 종료일자
A 20230101 20230101
A 20230103 20230104
A 20230106 20230107
B 20230103 20230103
B 20230106 20230107
고수님들 답변 부탁 드립니다.
WITH t AS ( SELECT 'A' cd, '20230101' dt FROM dual UNION ALL SELECT 'A' , '20230103' FROM dual UNION ALL SELECT 'A' , '20230104' FROM dual UNION ALL SELECT 'B' , '20230103' FROM dual UNION ALL SELECT 'A' , '20230106' FROM dual UNION ALL SELECT 'A' , '20230107' FROM dual UNION ALL SELECT 'B' , '20230106' FROM dual UNION ALL SELECT 'B' , '20230107' FROM dual ) SELECT cd , MIN(dt) sdt , MAX(dt) edt FROM (SELECT cd, dt FROM t ORDER BY cd, dt) GROUP BY cd, TO_DATE(dt, 'yyyymmdd') - ROWNUM ORDER BY cd, sdt ; -- http://gurubee.net/lecture/2194