CODEWITHSUNDEEP
sqloracle-databasedate-range
Pato
Pato

Reputation: 311

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

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?

Upvotes: 3

Views: 331

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Use window functions to see if there is any overlap with previous records. Then a cumulative sum to assign a "grouping" and aggregate.

I like to use a cumulative max for more generic overlaps:

select po, min(startdate), max(enddate)
from (select o.*,
             sum(case when prev_enddate >= startdate then 0 else 1 end) over (partition by po order by startdate) as grouping
      from (select o.*,
                   max(enddate) over (partition by po order by startdate range between unbounded preceding and '1' second preceding) as prev_enddate
            from orders o
           ) o
     ) o
group by po, grouping;

In many cases, you can use lag() instead of max():

select po, min(startdate), max(enddate)
from (select o.*,
             sum(case when prev_enddate >= startdate then 0 else 1 end) over (partition by po order by startdate) as grouping
      from (select o.*,
                   lag(enddate) over (partition by po order by startdate) as prev_enddate
            from orders o
           ) o
     ) o
group by po, grouping;

This works so long as the previous row has the overlap, which is typically the case.

Upvotes: 0

user5683823
user5683823

Reputation:

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)  
);

Upvotes: 2

Related Questions