Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
with t(id, start_dt, end_dt,type) as
  (
   select 1, date'2019-01-01', date'2019-02-21', 'cross' from dual union all
   select 1, date'2019-02-22', date'2019-06-05', 'cross'  from dual union all
   select 1, date'2019-06-06', date'2019-08-31', 'cross'  from dual union all  
   select 1, date'2019-09-01', date'2019-10-03', 'AAAA'  from dual union all
   select 1, date'2019-09-04', date'2019-10-04', 'cross'  from dual union all   
   select 1, date'2019-10-05', date'2019-10-06', 'AAAA'  from dual union all
   select 1, date'2019-10-07', date'2019-10-10', 'AAAA'  from dual union all  
   select 1, date'2019-10-11', date'2019-12-31', 'cross'  from dual union all
   select 2, date'2019-01-01', date'2019-02-21', 'cross' from dual union all
   select 2, date'2019-03-22', date'2019-06-05', 'cross'  from dual union all
   select 2, date'2019-06-06', date'2019-08-31', 'cross'  from dual   
  )
select *
from t

11 rows affected

select *
from t


ID
START_DT
END_DT
TYPE


1
01-JAN-19
21-FEB-19
cross

1
22-FEB-19
05-JUN-19
cross

1
06-JUN-19
31-AUG-19
cross

1
01-SEP-19
03-OCT-19
AAAA

1
04-SEP-19
04-OCT-19
cross

1
05-OCT-19
06-OCT-19
AAAA

1
07-OCT-19
10-OCT-19
AAAA

1
11-OCT-19
31-DEC-19
cross

2
01-JAN-19
21-FEB-19
cross

2
22-MAR-19
05-JUN-19
cross

2
06-JUN-19
31-AUG-19
cross


select id, type, min(start_dt), max(end_dt)
from (select t.*,
             sum(case when prev_end_dt >= start_dt - 1 then 0 else 1 end) over (partition by id, type order by start_dt) as grp
      from (select t.*,
                   lag(end_dt) over (partition by id, type order by start_dt) as prev_end_dt
            from t
           ) t
     ) t
group by id, type, grp
order by id, min(start_dt)


ID
TYPE
MIN(START_DT)
MAX(END_DT)


1
cross
01-JAN-19
31-AUG-19

1
AAAA
01-SEP-19
03-OCT-19

1
cross
04-SEP-19
04-OCT-19

1
AAAA
05-OCT-19
10-OCT-19

1
cross
11-OCT-19
31-DEC-19

2
cross
01-JAN-19
21-FEB-19

2
cross
22-MAR-19
31-AUG-19

ID	START_DT	END_DT	TYPE
1	01-JAN-19	21-FEB-19	cross
1	22-FEB-19	05-JUN-19	cross
1	06-JUN-19	31-AUG-19	cross
1	01-SEP-19	03-OCT-19	AAAA
1	04-SEP-19	04-OCT-19	cross
1	05-OCT-19	06-OCT-19	AAAA
1	07-OCT-19	10-OCT-19	AAAA
1	11-OCT-19	31-DEC-19	cross
2	01-JAN-19	21-FEB-19	cross
2	22-MAR-19	05-JUN-19	cross
2	06-JUN-19	31-AUG-19	cross