How to Find Gaps in Dates with SQL Pattern Matching

Statement1

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.

Statement2

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.

Statement3

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

Statement processed.

Statement4

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

Statement5

alter session set nls_date_format = '  HH24:MI  '

Statement processed.

Statement6

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

Statement7

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

Statement8

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.

Statement9

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

Statement10

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