find at least 2 consecutive items based on date ranges

Question

there are lot of solutions of similar question but based only one date column.

I would like to know maybe better solution for this to solve, I am attaching my solution but I find it a little bit complicated if you know better approach to this please post it.

here is table with orders with start and end dates for 2 items. I would like to print at least 2 consecutive rows based on date and item.

   ITEM , START , END
1. A, 01.01.2020, 31.01.2020
2. A,   01.02.2020, 31.03.2020
3. B,   01.02.2020, 30.04.2020
4. A,   01.05.2020, 30.06.2020
5. B,   01.06.2020, 31.07.2020
6. B,   01.09.2020, 30.09.2020
7. A,   01.08.2020, 31.10.2020
8. B,   01.10.2020, 31.10.2020
9. B,   01.11.2020, 31.12.2020

the output should be rows 1 and 2 for item A and rows 6,8 and 9 for item B

here is my approuch to this

with pool as (
                    select ITEM, START_DATE, END_DATE,
                              nvl(lag(end_date,1) over (partition by item order by end_date),START_DATE-1) prev_End_Date                    
                    from orders  )
, pool2 as     (
                    select item ,
                              START_DATE, END_DATE,
                              sum(case when PREV_END_DATE+1 = START_DATE then 0 else 1 end ) over (partition by item order by START_DATE) grp
                              from pool )
select item,start_date,end_date from (
select 
          ITEM, 
          START_DATE,
          END_DATE,
          grp,
          count(grp) over (partition by item,grp ) cnt
 from pool2)
 where cnt>=2
 ;

score 0 · Accepted Answer · answered Oct 11 '20 at 13:33

Hmmm . . . use lag() and lead() to see the next/previous values and check if they match:

select o.*
from (select o.*,
             lag(end) over (partition by product order by start) as prev_end,
             lead(start) over (partition by product order by start) as next_start
      from orders o
     ) o
where start = prev_end + interval '1' day or
      end = next_start - interval '1' day;

score 0 · Answer 2 · answered Oct 11 '20 at 14:53

-- create table and insert rows for test

Create table order_overlap (id number, item varchar2(1), start_date date , end_date date );
 

insert into order_overlap(id,start_date, end_date, item) values( 1,to_date('01.01.2020', 'dd.mm.yyyy'), to_date( '31.01.2020', 'dd.mm.yyyy'), 'A');
insert into order_overlap(id,start_date, end_date, item) values( 2, to_date('01.02.2020', 'dd.mm.yyyy'), to_date( '31.03.2020', 'dd.mm.yyyy'), 'A');
insert into order_overlap(id,start_date, end_date, item) values( 3, to_date('01.02.2020', 'dd.mm.yyyy'), to_date( '30.04.2020', 'dd.mm.yyyy'), 'B');
insert into order_overlap(id,start_date, end_date, item) values( 4, to_date('01.05.2020', 'dd.mm.yyyy'), to_date( '30.06.2020', 'dd.mm.yyyy'), 'A');
insert into order_overlap(id,start_date, end_date, item) values( 5, to_date('01.06.2020', 'dd.mm.yyyy'), to_date( '31.07.2020', 'dd.mm.yyyy'), 'B');
insert into order_overlap(id,start_date, end_date, item) values( 6, to_date('01.09.2020', 'dd.mm.yyyy'), to_date( '30.09.2020', 'dd.mm.yyyy'), 'B');
insert into order_overlap(id,start_date, end_date, item) values( 7, to_date('01.08.2020', 'dd.mm.yyyy'), to_date( '31.10.2020', 'dd.mm.yyyy'), 'A');
insert into order_overlap(id,start_date, end_date, item) values( 8, to_date('01.10.2020', 'dd.mm.yyyy'), to_date( '31.10.2020', 'dd.mm.yyyy'), 'B');
insert into order_overlap(id,start_date, end_date, item) values( 5, to_date('01.11.2020', 'dd.mm.yyyy'), to_date( '31.12.2020', 'dd.mm.yyyy'), 'B');

-- I did sth a little bit different but maybe you like it. -- I joined conseutive rows into one - so if you have item A 01.01.2020 - 31.01.2020 A 01.02.2020 - 28.02.2020

you get one recod A 01.01.2020 - 28.02.2020

 select item, min(start_date) start_date , max(end_date) end_date, count(*)  
 from (  
   select item, start_date, end_date,  
     case when lead(start_date) over(partition by item order by start_date) = end_date + 1   
      OR lag(end_date) over(partition by item order by end_date) + 1 = start_date   
      then 0  
      else rownum  
     end continuity  
   from order_overlap ) 
 group by item, continuity
 order by item, start_date;

yes that is another type of output thanks but how would you exclude the item B records if I wanted exact 2 consecutive rows so only the item A will be in the output — Pato, Oct 11 '20 at 21:18

score 0 · Answer 3 · answered Oct 11 '20 at 19:02

You can simply use MATCH_RECOGNIZE to perform a row-by-row comparison and to only return the groups of rows which match the pattern:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY item
  ORDER BY start_date, end_date
  ALL ROWS PER MATCH
  PATTERN ( FIRST_ROW NEXT_ROWS+ )
  DEFINE
    NEXT_ROWS AS (
      NEXT_ROWS.START_DATE = PREV( END_DATE ) + INTERVAL '1' DAY
    )
)

So, for your sample data:

CREATE TABLE table_name ( ITEM, START_DATE, END_DATE ) AS
SELECT 'A', DATE '2020-01-01', DATE '2020-01-31' FROM DUAL UNION ALL
SELECT 'A', DATE '2020-02-01', DATE '2020-03-31' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-02-01', DATE '2020-04-30' FROM DUAL UNION ALL
SELECT 'A', DATE '2020-05-01', DATE '2020-06-30' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-06-01', DATE '2020-07-31' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-09-01', DATE '2020-09-30' FROM DUAL UNION ALL
SELECT 'A', DATE '2020-08-01', DATE '2020-10-31' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-10-01', DATE '2020-10-31' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-11-01', DATE '2020-12-31' FROM DUAL;

This outputs:

ITEM | START_DATE | END_DATE  
:--- | :--------- | :---------
A    | 2020-01-01 | 2020-01-31
A    | 2020-02-01 | 2020-03-31
B    | 2020-09-01 | 2020-09-30
B    | 2020-10-01 | 2020-10-31
B    | 2020-11-01 | 2020-12-31

db<>fiddle here

with my approach I have the ability to choose the number of consecutive rows,let's say I want exact 2 consecutive rows, so the item B will be not present in the output, how would you achieve this with match_recognize? — Pato, Oct 11 '20 at 21:14
@PatrikMelichercik That is not one of the requirements in the question; however, its relatively simple to add a `MEASURES` clause to `COUNT` the size of the group and then filter on that [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b30b7721da551d9862847178d7315d1d). — MT0, Oct 11 '20 at 21:48

find at least 2 consecutive items based on date ranges

3 Answers3