WITH a AS
(
SELECT '갑돌이' usr, '20080901' sdt, '20080910' edt FROM dual
UNION ALL SELECT '갑돌이', '20080920', '20080930' FROM dual
UNION ALL SELECT '갑순이', '20080910', '20080915' FROM dual
UNION ALL SELECT '홍길동', '20080910', '20080915' FROM dual
UNION ALL SELECT '홍길동', '20080915', '20080925' FROM dual
UNION ALL SELECT '홍길동', '20080925', '20080930' FROM dual
UNION ALL SELECT '일지매', '20080902', '20080930' FROM dual
UNION ALL SELECT '삼식이', '20080915', '20080920' FROM dual
UNION ALL SELECT '삼순이', '20080901', '20080930' FROM dual
)
, b AS
(
SELECT '갑돌이' usr, '20080903' sdt, '20080916' edt FROM dual
UNION ALL SELECT '갑돌이', '20080923', '20080926' FROM dual
UNION ALL SELECT '갑순이', '20080929', '20080930' FROM dual
UNION ALL SELECT '홍길동', '20080923', '20080925' FROM dual
UNION ALL SELECT '삼식이', '20080901', '20080930' FROM dual
UNION ALL SELECT '삼순이', '20080905', '20080910' FROM dual
UNION ALL SELECT '삼순이', '20080913', '20080917' FROM dual
UNION ALL SELECT '삼순이', '20080921', '20090924' FROM dual
)
SELECT usr
     , MIN(dt) sdt
     , MAX(dt) edt
  FROM (SELECT usr, dt
             , SUM(gb) OVER(PARTITION BY usr ORDER BY dt) gb
          FROM (SELECT a.usr
                     , TO_CHAR(a.dt,'yyyymmdd') dt
                     , CASE WHEN a.dt - 1 = LAG(a.dt) OVER(PARTITION BY a.usr ORDER BY a.dt)
                            THEN 0 ELSE 1 END gb
                  FROM (SELECT a.usr
                             , TO_DATE(a.sdt,'yyyymmdd') + lv - 1 dt
                          FROM a
                             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) c
                         WHERE TO_DATE(a.edt,'yyyymmdd') - TO_DATE(a.sdt,'yyyymmdd') + 1 >= c.lv
                        ) a
                     , b
                 WHERE a.usr = b.usr(+)
                   AND a.dt <= b.edt(+)
                   AND a.dt >= b.sdt(+)
                   AND b.usr IS NULL
                )
        )
 GROUP BY usr, gb
 ORDER BY usr, gb