How to Find Gaps in Dates with SQL Pattern Matching

View All Scripts Login to Run Script

Script Name How to Find Gaps in Dates with SQL Pattern Matching
Description This shows you how to find gaps in rows containing start/end dates using match_recognize. You can make this a reusable SQL fragment by placing this in a SQL macro.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Wednesday September 23, 2020

Statement 1

Create the Table

create table meeting_attendees (  
  attendee_id integer  
    not null,  
  start_date date  
    not null,  
  end_date date,  
  primary key (   
    attendee_id, start_date  
  )  
)

Table created.

Statement 2

This loads meeting requests for two people. The aim is to find timeslots where both people are available (they have no scheduled meetings).

Load the Data

begin   
  insert into meeting_attendees   
    values ( 1, date'2020-06-17' + 9/24, date'2020-06-17' + 10/24 );  
  insert into meeting_attendees   
    values ( 1, date'2020-06-17' + 10/24, date'2020-06-17' + 10.5/24 );  
  insert into meeting_attendees   
    values ( 1, date'2020-06-17' + 11/24, date'2020-06-17' + 13/24 );  
  insert into meeting_attendees   
    values ( 1, date'2020-06-17' + 16/24, date'2020-06-17' + 17/24 );  
      
  insert into meeting_attendees   
    values ( 2, date'2020-06-17' + 9/24, date'2020-06-17' + 12/24 );  
  insert into meeting_attendees   
    values ( 2, date'2020-06-17' + 12.5/24, date'2020-06-17' + 13/24 );  
  insert into meeting_attendees   
    values ( 2, date'2020-06-17' + 13/24, date'2020-06-17' + 14/24 );  
  insert into meeting_attendees   
    values ( 2, date'2020-06-17' + 15/24, date'2020-06-17' + 17/24 );  
end;

Statement processed.

Statement 3

alter session set nls_date_format = '  DD Mon YY HH24:MI  '

Statement processed.

Statement 4

The aim is to find blocks of time where both people are free. There is one gap in these data, from 2pm to 3pm

select * from meeting_attendees  
order by start_date, end_date


ATTENDEE_ID START_DATE END_DATE
1 17 Jun 20 09:00 17 Jun 20 10:00
2 17 Jun 20 09:00 17 Jun 20 12:00
1 17 Jun 20 10:00 17 Jun 20 10:30
1 17 Jun 20 11:00 17 Jun 20 13:00
2 17 Jun 20 12:30 17 Jun 20 13:00
2 17 Jun 20 13:00 17 Jun 20 14:00
2 17 Jun 20 15:00 17 Jun 20 17:00
1 17 Jun 20 16:00 17 Jun 20 17:00

8 rows selected.

ATTENDEE_ID	START_DATE	END_DATE
1	17 Jun 20 09:00	17 Jun 20 10:00
2	17 Jun 20 09:00	17 Jun 20 12:00
1	17 Jun 20 10:00	17 Jun 20 10:30
1	17 Jun 20 11:00	17 Jun 20 13:00
2	17 Jun 20 12:30	17 Jun 20 13:00
2	17 Jun 20 13:00	17 Jun 20 14:00
2	17 Jun 20 15:00	17 Jun 20 17:00
1	17 Jun 20 16:00	17 Jun 20 17:00

Statement 5

alter session set nls_date_format = '  HH24:MI  '

Statement processed.

Statement 6

There is a free slot in both calendars when the latest end date so far is before the next start date (sorted by start date, end date). The gap falls between two rows. So this pattern needs to match rows you want to exclude from the results. You can do this by wrapping the pattern variable in the exclusion operation - {- -}.

Find the Gaps

select *   
from   meeting_attendees match_recognize (  
    order by start_date, end_date  
    measures  
      max ( end_date ) start_gap,   
      next ( start_date ) end_gap,  
      classifier() as cls  
    all rows per match   
    pattern ( ( gap | {-no_gap-} )+ )  
    define gap as max ( end_date ) < next ( start_date )  
  )


START_DATE END_DATE START_GAP END_GAP CLS ATTENDEE_ID
13:00 14:00 14:00 15:00 GAP 2

START_DATE	END_DATE	START_GAP	END_GAP	CLS	ATTENDEE_ID
13:00	14:00	14:00	15:00	GAP	2

Statement 7

You can display all the input rows to pattern matching with all rows per match with unmatched rows. But this is incompatible with the exclusion operation. Because NO_GAP is an always true condition, to view these rows remove the exclusion operation from this variable.

Show the All the Rows

select cls, end_date, start_gap, end_gap   
from   meeting_attendees match_recognize (  
    order by start_date, end_date  
    measures  
      max ( end_date ) start_gap,   
      next ( start_date ) end_gap,  
      classifier() as cls  
    all rows per match   
    pattern ( ( gap | no_gap )+ )  
    define gap as max ( end_date ) < next ( start_date )  
  )


CLS END_DATE START_GAP END_GAP
NO_GAP 10:00 10:00 09:00
NO_GAP 12:00 12:00 10:00
NO_GAP 10:30 12:00 11:00
NO_GAP 13:00 13:00 12:30
NO_GAP 13:00 13:00 13:00
GAP 14:00 14:00 15:00
NO_GAP 17:00 17:00 16:00
NO_GAP 17:00 17:00  - 

8 rows selected.

CLS	END_DATE	START_GAP	END_GAP
NO_GAP	10:00	10:00	09:00
NO_GAP	12:00	12:00	10:00
NO_GAP	10:30	12:00	11:00
NO_GAP	13:00	13:00	12:30
NO_GAP	13:00	13:00	13:00
GAP	14:00	14:00	15:00
NO_GAP	17:00	17:00	16:00
NO_GAP	17:00	17:00	-

Statement 8

You can turn this into a reusable fragment by placing the match_recognize clause inside a SQL macro.

Make it Reusable with SQL Macros

create or replace function find_gaps (  
  tab            dbms_tf.table_t,   
  date_cols      dbms_tf.columns_t  
)  
  return varchar2   
  sql_macro   
as  
begin  
  return 'find_gaps.tab match_recognize (   
  order by ' || find_gaps.date_cols ( 1 ) || ', ' || find_gaps.date_cols ( 2 ) || '    
  measures     
    max ( ' || find_gaps.date_cols ( 2 ) || ' ) start_gap,   
    next ( ' || find_gaps.date_cols ( 1 ) || ' ) end_gap  
  all rows per match  
  pattern ( ( gap | {-no_gap-} )+ )    
  define     
    gap as max ( ' || find_gaps.date_cols ( 2 ) || ' ) < (   
      next ( ' || find_gaps.date_cols ( 1 ) || ' )  
    )    
)';  
end find_gaps;

Function created.

Statement 9

You can now pass any table with start/end date columns to the macro to find any gaps in the data.

Calling a SQL Macro

select start_gap, end_gap  
from   find_gaps (   
  meeting_attendees,   
  columns ( start_date, end_date )   
)


START_GAP END_GAP
14:00 15:00

START_GAP	END_GAP
14:00	15:00

Statement 10

To get the query generated by the SQL macro, pass it to dbms_utility.expand_sql_text.

View the Final SQL Query

declare  
  l_clob clob;  
begin  
  dbms_utility.expand_sql_text (  
    input_sql_text  => q'!select *   
from   find_gaps (   
  meeting_attendees,   
  columns ( start_date, end_date )   
)!',  
    output_sql_text => l_clob  );  
  dbms_output.put_line(l_clob);  
end;

Statement processed.
SELECT "A1"."START_DATE" "START_DATE","A1"."END_DATE" "END_DATE","A1"."START_GAP" "START_GAP","A1"."END_GAP" "END_GAP","A1"."ATTENDEE_ID" "ATTENDEE_ID" FROM  (SELECT "A3"."START_DATE" "START_DATE","A3"."END_DATE" "END_DATE","A3"."START_GAP" "START_GAP","A3"."END_GAP" "END_GAP","A3"."ATTENDEE_ID" "ATTENDEE_ID" FROM  (SELECT  *  FROM  (SELECT "A2"."ATTENDEE_ID" "ATTENDEE_ID","A2"."START_DATE" "START_DATE","A2"."END_DATE" "END_DATE" FROM "SQL_XYFAXMHMJFABNKUWUHJFVDJLY"."MEETING_ATTENDEES" "A2") "A4" MATCH_RECOGNIZE (  ORDER BY "START_DATE","END_DATE" MEASURES MAX("END_DATE") AS "START_GAP",NEXT("START_DATE") AS "END_GAP" ALL ROWS PER MATCH  SHOW EMPTY MATCHES  AFTER MATCH SKIP PAST LAST ROW  PATTERN ( (("GAP" |  {- "NO_GAP" -} ))+) DEFINE "GAP" AS MAX("END_DATE")

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database