print start and end date in one row for continous or overlaping date ranges in oracle SQL

Question

I would like to print in one row start date and end date for continous or overlaping date ranges.

here is the data

create table orders (
po varchar2(6),
startdate date,
enddate date
);

insert into orders values ('order1',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order1',to_date('01-02-2020','dd-MM-yyyy'),to_date('31-03-2020','dd-MM-yyyy'));
insert into orders values ('order1',to_date('01-04-2020','dd-MM-yyyy'),to_date('30-06-2020','dd-MM-yyyy'));
insert into orders values ('order2',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order2',to_date('01-03-2020','dd-MM-yyyy'),to_date('31-03-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('02-02-2020','dd-MM-yyyy'),to_date('31-05-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('01-05-2020','dd-MM-yyyy'),to_date('31-07-2020','dd-MM-yyyy'));

expected output is

order1  01-01-2020   30-06-2020
order2  01-01-2020   31-01-2020
order2  01-03-2020   31-03-2020
order3  01-01-2020   31-01-2020
order3  02-02-2020   31-07-2020

first I tried to use unpivot clause to get all dates in one column and to check previous and following rows if they are overlaping or continous and then eliminate this rows but it won't work because if there is overlap the order of dates will be not startdate following by enddate anymore.

this won't work as a starting point

select * from(
select * from (
select po,startdate,enddate from orders)
unpivot(column_val for column_name in (startdate,enddate)) )order by po,column_val

any other solutions for that?

Answer 1

There is an elegant (and efficient) solution using the match_recognize clause (which requires Oracle 12.1 or higher).

select po, startdate, enddate
from   orders
match_recognize (
  partition by po
  order     by startdate
  measures  first(startdate) as startdate, max(enddate) as enddate
  pattern   ( c* n )
  define    c as max(enddate) + 1 >= next(startdate)  
);