By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T
(clientid int, startdate date, enddate date)
;
insert into T
select 1, date'2017-10-10', date'2017-10-12' from dual union all
select 1, date'2017-10-12', date'2017-10-13' from dual union all
select 1, date'2017-10-13', date'2017-10-17' from dual union all
select 1, date'2017-11-10', date'2017-11-17' from dual union all
select 1, date'2017-11-17', date'2017-11-23' from dual union all
select 1, date'2017-12-12', date'2017-12-14' from dual union all
select 2, date'2017-11-10', date'2017-11-15' from dual union all
select 2, date'2017-12-01', date'2017-12-02' from dual union all
select 2, date'2017-12-02', date'2017-12-05' from dual;
9 rows affected
select clientid,
decode(nvl(lag(enddate) over
(order by enddate),startdate),startdate,0,1)
as grp, --> means prev. value equals or not
row_number() over (order by clientid, enddate) as rn,
startdate, enddate
from t
| CLIENTID | GRP | RN | STARTDATE | ENDDATE |
|---|---|---|---|---|
| 1 | 0 | 1 | 10-OCT-17 | 12-OCT-17 |
| 1 | 0 | 2 | 12-OCT-17 | 13-OCT-17 |
| 1 | 0 | 3 | 13-OCT-17 | 17-OCT-17 |
| 1 | 1 | 4 | 10-NOV-17 | 17-NOV-17 |
| 1 | 0 | 5 | 17-NOV-17 | 23-NOV-17 |
| 1 | 1 | 6 | 12-DEC-17 | 14-DEC-17 |
| 2 | 1 | 7 | 10-NOV-17 | 15-NOV-17 |
| 2 | 1 | 8 | 01-DEC-17 | 02-DEC-17 |
| 2 | 0 | 9 | 02-DEC-17 | 05-DEC-17 |
select tt.*, sum(grp) over (order by clientid, startdate) sm
from
(
select clientid,
decode(nvl(lag(enddate) over
(order by enddate),startdate),startdate,0,1)
as grp, --> means prev. value equals or not
row_number() over (order by clientid, enddate) as rn,
startdate, enddate
from t
) tt
order by rn
| CLIENTID | GRP | RN | STARTDATE | ENDDATE | SM |
|---|---|---|---|---|---|
| 1 | 0 | 1 | 10-OCT-17 | 12-OCT-17 | 0 |
| 1 | 0 | 2 | 12-OCT-17 | 13-OCT-17 | 0 |
| 1 | 0 | 3 | 13-OCT-17 | 17-OCT-17 | 0 |
| 1 | 1 | 4 | 10-NOV-17 | 17-NOV-17 | 1 |
| 1 | 0 | 5 | 17-NOV-17 | 23-NOV-17 | 1 |
| 1 | 1 | 6 | 12-DEC-17 | 14-DEC-17 | 2 |
| 2 | 1 | 7 | 10-NOV-17 | 15-NOV-17 | 3 |
| 2 | 1 | 8 | 01-DEC-17 | 02-DEC-17 | 4 |
| 2 | 0 | 9 | 02-DEC-17 | 05-DEC-17 | 4 |
select clientid, min(startdate) as startdate, max(enddate) as enddate
from
(
select tt.*, sum(grp) over (order by clientid, startdate) sm
from
(
select clientid,
decode(nvl(lag(enddate) over
(order by enddate),startdate),startdate,0,1)
as grp, --> means prev. value equals or not
row_number() over (order by clientid, enddate) as rn,
startdate, enddate
from t
) tt
order by rn
)
group by clientid, sm
order by clientid, enddate;
| CLIENTID | STARTDATE | ENDDATE |
|---|---|---|
| 1 | 10-OCT-17 | 17-OCT-17 |
| 1 | 10-NOV-17 | 23-NOV-17 |
| 1 | 12-DEC-17 | 14-DEC-17 |
| 2 | 10-NOV-17 | 15-NOV-17 |
| 2 | 01-DEC-17 | 05-DEC-17 |