그냥 MIN MAX 로는 해결이 안되서 도움 요청 드려요
사람별 시작일 종료일을 관리하는데 해당 일수저장 된 데이터가
2020-01-01 ~ 2020-02-28
2020-03-01 ~ 2020-03-15
2020-06-01 ~ 2020-08-31
이렇게 세개의 로우 데이터로 관리 되고 있습니다.
하지만 세 로우 데이터를 표현을 하고자 할때
연달아 이어지는 데이터는 한줄로 표현하고 중간에 공백이 있으면 새롭게 또 나오는 데이터이여야 합니다.
2020-01-01 ~ 2020-03-15
2020-06-01 ~ 2020-08-31
관련하여 도움 주세요 ㅜㅜ
SELECT '홍길동' AS NM
,'20200101' AS FRM_DT
,'20200228' AS TO_DT
FROM DUAL
UNION ALL
SELECT '홍길동' AS NM
,'20200301' AS FRM_DT
,'20200315'
FROM DUAL
UNION ALL
SELECT '홍길동' AS NM
,'20200601' AS FRM_DT
,'20200831'
FROM DUAL
-- 2020년 2월은 29일까지 있습니다 ㅎㅎ WITH T AS ( SELECT '홍길동' AS NM ,'20200101' AS FRM_DT ,'20200229' AS TO_DT FROM DUAL UNION ALL SELECT '홍길동' AS NM ,'20200301' AS FRM_DT ,'20200315' FROM DUAL UNION ALL SELECT '홍길동' AS NM ,'20200601' AS FRM_DT ,'20200831' FROM DUAL ) SELECT nm, MIN(frm_dt) AS frm_dt, MAX(to_dt) AS to_dt FROM ( SELECT nm, frm_dt, to_dt , SUM(gb) OVER(PARTITION BY nm ORDER BY frm_dt) AS gb FROM ( SELECT nm, frm_dt, to_dt , DECODE(frm_dt, LAG(to_dt, 1) OVER(PARTITION BY nm ORDER BY frm_dt) + 1, 0, 1) AS gb FROM ( SELECT nm, TO_DATE(frm_dt, 'yyyymmdd') AS frm_dt, TO_DATE(to_dt, 'yyyymmdd') AS to_dt FROM T ) ) ) GROUP BY nm, gb ORDER BY nm, frm_dt