'SQL. Locating gaps between a series of fromDates and toDates

I have some data in an Oracle table as follows:

ROW    USERNAME DATEFROM    DATETO
1      User01   15/03/2020  17/06/2020
2      User01   18/06/2020  21/05/2021
3      User01   22/05/2021  12/08/2021
4      User02   27/07/2021  14/09/2021
5      User02   20/09/2021  15/12/2021 --Offending row.

I have included the Row ID in this example for clarity. In my real table, there is no sequential rowID. I'm looking for rows having gaps from DateFrom to DateTo across records. In the above data, for example, RowID 2 is OK because DateFrom is 18/06/2020 which is the next day from DateTo in row 1. The error is in row 5 because there is a gap between DateTo in row 4 and DateFrom in row 5. DateFrom in row 5 should have been 15/09/2021 to be correct. I need an SQL script to locate all the gaps in a table of several thousand records. Thanks for your help.

sqloracle


Solution 1:[1]

Besides the earliest row in your table, the below query should select everything that does not immediately follow another date.

SELECT * FROM Table
WHERE DATEFROM - 1 NOT IN (SELECT DATETO FROM Table)

Solution 2:[2]

Here's one option:

Sample data:

SQL> with test (username, datefrom, dateto) as
  2    (select 'user1', date '2020-03-15', date '2020-06-17' from dual union all
  3     select 'user1', date '2020-06-18', date '2021-05-21' from dual union all
  4     select 'user1', date '2021-05-22', date '2021-08-12' from dual union all
  5     --
  6     select 'user2', date '2021-07-27', date '2021-09-14' from dual union all
  7     select 'user2', date '2021-09-20', date '2021-12-15' from dual
  8    ),

Query begins here; the temp CTE "calculates" the next DATEFROM using the LEAD analytic function. The final query then fetches mismatched rows:

  9  temp as
 10    (select username, datefrom, dateto,
 11       lead(datefrom) over (partition by username order by datefrom) next_datefrom
 12     from test
 13    )
 14  select username, datefrom, dateto
 15  from temp
 16  where next_datefrom <> dateto + 1;

USERN DATEFROM   DATETO
----- ---------- ----------
user2 27/07/2021 14/09/2021

SQL>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Matthew Shoop
Solution 2 Littlefoot

Related Questions