By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name
(ID int, start_Date date, end_Date date)
;
INSERT INTO table_name
select 001, TO_DATE('2005-01-01','YYYY-MM-DD'), TO_DATE('2006-01-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2005-01-01','YYYY-MM-DD'), TO_DATE('2007-01-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2008-01-01','YYYY-MM-DD'), TO_DATE('2008-06-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2008-04-01','YYYY-MM-DD'), TO_DATE('2008-12-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2010-01-01','YYYY-MM-DD'), TO_DATE('2010-05-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2010-04-01','YYYY-MM-DD'), TO_DATE('2010-12-01','YYYY-MM-DD') from dual
union all select 001, TO_DATE('2010-11-01','YYYY-MM-DD'), TO_DATE('2012-01-01','YYYY-MM-DD') from dual
;
7 rows affected
with u as
(select ID, start_date, end_date,
case
when start_date <= lag(end_date) over(partition by ID order by start_date, end_date) then 0
else 1 end as grp
from table_name),
v as
(select ID, start_date, end_date,
sum(grp) over(partition by ID order by start_date, end_date) as island
from u)
select ID, min(start_date) as start_Date, max(end_date) as end_date
from v
group by ID, island;
| ID | START_DATE | END_DATE |
|---|---|---|
| 1 | 01-JAN-05 | 01-JAN-07 |
| 1 | 01-JAN-08 | 01-DEC-08 |
| 1 | 01-JAN-10 | 01-JAN-12 |