WITH S AS ( SELECT 'a' AA, TO_DATE('20150101','yyyymmdd') ST, TO_DATE('20150131','yyyymmdd') DT FROM DUAL UNION ALL SELECT 'a' AA, TO_DATE('20150111','yyyymmdd') ST, TO_DATE('20150121','yyyymmdd') DT FROM DUAL UNION ALL SELECT 'B' AA, TO_DATE('20150101','yyyymmdd') ST, TO_DATE('20150130','yyyymmdd') DT FROM DUAL UNION ALL SELECT 'B' AA, TO_DATE('20150131','yyyymmdd') ST, TO_DATE('20150221','yyyymmdd') DT FROM DUAL ) SELECT AA, MIN(DT) - MAX(ST) + 1 FROM S GROUP BY AA ;
WITH s AS ( SELECT 1 pk, 'a' aa, TO_DATE('20150101', 'yyyymmdd') st, TO_DATE('20150131', 'yyyymmdd') dt FROM dual UNION ALL SELECT 2, 'a', TO_DATE('20150111', 'yyyymmdd'), TO_DATE('20150121', 'yyyymmdd') FROM dual ) -- 겹치는 구간 검색은 시작과 종료를 서로 교차하여 비교합니다. SELECT a.* , b.* , GREATEST(a.st, b.st) sdt , LEAST(a.dt, b.dt) edt , LEAST(a.dt, b.dt) - GREATEST(a.st, b.st) + 1 AS cnt FROM s a , s b WHERE a.aa = b.aa AND a.pk != b.pk AND a.st <= b.dt AND a.dt >= b.st ;