24시가 애매하네요..


WITH A (NO, SDT, EDT) AS (
SELECT 1, '201512010000' , '201512110000' FROM DUAL UNION ALL
SELECT 1, '201512150000' , '201512210000' FROM DUAL 
),B (NO, SDT, EDT) AS (
SELECT 1, '201512020900' , '201512040000' FROM DUAL UNION ALL
SELECT 1, '201512161400' , '201512200000' FROM DUAL 
)
select min(dt) sdt, max(dt) edt
from(
select dt,TO_DATE(dt,'YYYYMMDDHH24MI') - (rownum/24) gb
from (
SELECT dt
FROM A,(SELECT TO_CHAR(TO_DATE('201512010000','YYYYMMDDHH24MI')+ ((LEVEL-1)/24),'YYYYMMDDHH24MI') dt 
        FROM DUAL 
        CONNECT BY LEVEL <= 745) c
where a.sdt <= dt and a.edt >= dt
minus
SELECT dt
FROM b,(SELECT TO_CHAR(TO_DATE('201512010000','YYYYMMDDHH24MI')+ ((LEVEL-1)/24),'YYYYMMDDHH24MI') dt 
        FROM DUAL 
        CONNECT BY LEVEL <= 745) c
where b.sdt <= dt and b.edt >= dt))
group by gb
order by sdt;

group by 방법에 이마를 탁 치고 갑니다.

엄청난 도움이 되었습니다.

감사합니다.

WITH a AS
(
SELECT 1 no, '2015120100' sdt, '2015121100' edt FROM dual
UNION ALL SELECT 1, '2015121500', '2015122100' FROM dual
)
, b AS
(
SELECT 1 no, '2015120209' sdt, '2015120400' edt FROM dual
UNION ALL SELECT 1, '2015121614', '2015122000' FROM dual
)
SELECT sdt, edt
  FROM (SELECT sdt
             , LEAD(sdt) OVER(ORDER BY sdt) edt
             , SUM(SUM(st)) OVER(ORDER BY sdt) st
          FROM (SELECT sdt, 1 st FROM a
                UNION ALL SELECT edt, -1 FROM a
                UNION ALL SELECT sdt, -1 FROM b
                UNION ALL SELECT edt,  1 FROM b
                )
         GROUP BY sdt
        )
 WHERE st = 1
;