oracle 중복날짜 제거 및 중복기간 나눠주는 쿼리

oracle 중복기간 나눠주는 쿼리 중복날짜 제거

with t1 as( 'a' usr,'2011-09-01' sdt, '2011-09-10' edt ,'2011-09-11' sdt1 , '2011-09-28' edt1 ... FROM dual )

,t as (

select * from t1

unpivot((sdt,edt) for dat in ((sdt,edt) as 1, (std1, edt1) as 2, (sdt2,edt2) as 3)

) 
SELECT usr 
, MIN(sdt) sdt 
, MAX(edt) edt 
FROM 
( 
SELECT usr, sdt, edt 
, SUM(flag) OVER(PARTITION BY usr ORDER BY sdt, edt) grp 
FROM 
( 
SELECT usr, sdt, edt 
, CASE WHEN TO_CHAR(TO_DATE(sdt, 'yyyy-mm-dd') - 1, 'yyyy-mm-dd') 
<= MAX(edt) OVER(PARTITION BY usr ORDER BY sdt, edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
THEN 0 ELSE 1 END flag 
FROM t 
) 
) 
GROUP BY usr, grp 
ORDER BY usr, grp 
;