시작 종료일자는 월단위라 보고 round처리 했습니다.
--윈도우함수 사용 with t(cd, sdt, edt) as ( select '1', '20160101', '20160331' from dual union all select '1', '20160201', '20160430' from dual union all select '1', '20160601', '20160930' from dual union all select '3', '20160801', '20161231' from dual union all select '4', '20160101', '20161231' from dual ) select cd, sum(months) months from ( select cd, gb, round(months_between(to_date(max(edt),'yyyymmdd') , to_date(min(sdt),'yyyymmdd'))) months from ( select cd, sdt, edt, sum(changed) over(partition by cd order by sdt, edt) gb from ( select cd, sdt, edt , case when max(edt) over(partition by cd order by sdt, edt rows between unbounded preceding and 1 preceding) >= sdt then 0 else 1 end changed from t ) ) group by cd, gb ) group by cd --재귀쿼리 사용 with t(cd, sdt, edt) as ( select '1', '20160101', '20160331' from dual union all select '1', '20160201', '20160430' from dual union all select '1', '20160601', '20160930' from dual union all select '3', '20160801', '20161231' from dual union all select '4', '20160101', '20161231' from dual ) , t2(cd, sdt, edt) as ( select cd, to_date(sdt,'yyyymmdd'), to_date(edt,'yyyymmdd') from t union all select cd, sdt + 1, edt from t2 where sdt + 1 <= edt ) select cd, count(distinct to_char(sdt,'yyyymm')) months from t2 group by cd --계층쿼리 사용 select cd, sum(months) months from ( select max(cd) cd, round(months_between(max(edt), min(sdt))) months from ( select connect_by_root(cd||sdt||edt) root, cd, sdt, edt from t a start with not exists (select 1 from t b where b.cd = a.cd and b.sdt != a.sdt and b.edt != a.edt and b.sdt <= a.sdt and b.edt >= a.sdt) connect by nocycle prior cd = cd and prior sdt != sdt and prior edt != edt and prior sdt <= edt and prior edt >= sdt ) group by root ) group by cd
WITH t AS ( SELECT 1 no, '20160101' sdt, '20160229' edt FROM dual UNION ALL SELECT 1, '20160101', '20160229' FROM dual UNION ALL SELECT 1, '20160601', '20160930' FROM dual -- 추가 테스트 --UNION ALL SELECT 3, '20160801', '20161231' FROM dual UNION ALL SELECT 3, '20160801', '20161130' FROM dual -- 변경 테스트 UNION ALL SELECT 4, '20160101', '20161231' FROM dual ) SELECT no , SUM(mm) mm FROM (SELECT no , MONTHS_BETWEEN(MAX(dt) + 1, MIN(dt)) * cnt mm FROM (SELECT MIN(no) no , sdt + lv - 1 dt , COUNT(*) KEEP(DENSE_RANK FIRST ORDER BY no) cnt , ROW_NUMBER() OVER(PARTITION BY MIN(no) ORDER BY sdt + lv - 1) rn FROM (SELECT no , TO_DATE(sdt, 'yyyymmdd') sdt , TO_DATE(edt, 'yyyymmdd') edt FROM t ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999) WHERE lv <= edt - sdt + 1 GROUP BY sdt + lv - 1 ) GROUP BY no, cnt, dt - rn ) GROUP BY no ORDER BY no ;
WITH T(연번,시작일자,종료일자) AS ( SELECT 1, TO_DATE('20160101','yyyymmdd'), TO_DATE('20160229','yyyymmdd') FROM dual UNION ALL SELECT 1, TO_DATE('20160101','yyyymmdd'), TO_DATE('20160229','yyyymmdd') FROM dual UNION ALL SELECT 3, TO_DATE('20160801','yyyymmdd'), TO_DATE('20161231','yyyymmdd') FROM dual UNION ALL SELECT 4, TO_DATE('20160101','yyyymmdd'), TO_DATE('20161231','yyyymmdd') FROM dual ) SELECT 연번, SUM(cnt) 날짜합계 FROM ( SELECT dt, 연번, cnt FROM ( SELECT dt, 연번, cnt, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY 연번) rn FROM ( SELECT dt, 연번, COUNT(*) cnt FROM ( SELECT 연번, 시작일자 + lv - 1 dt FROM T, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 1000) WHERE lv <= 종료일자 - 시작일자 + 1 ) GROUP BY dt, 연번 ) ) WHERE rn = 1 ) GROUP BY 연번 ORDER BY 연번 모법답안은 위에 있지만.. 날짜 갯수 세는걸로 해봤습니다.