Find gaps in SQL between datetime Ranges within a startdatetime and enddatetime value

Question

im looking for a SQL Query that can deliver me all of the free time Intervals between a given Range for a Table with two datetime Columns (DATE_FROM, DATE_TILL). As a Requirement: All other entries are not overlapped and the only acceptable distance between each interval is 1 Second. I have found a Solution but this doesnt fill all my Requirements, specially the one where i want to put a given start and end datetime to calculate the missing Intervals if given.

Here is my datatable:

ROW_ID LOCATION_ID   DATE_FROM              DATE_TILL
1       193          2019-02-01 00:00:00    2019-12-31 23:59:59
2       193          2020-02-01 00:00:00    2020-12-31 23:59:59
3       193          2021-01-01 00:00:00    2021-12-31 23:59:59
4       193          2022-01-01 00:00:00    2022-12-31 23:59:59
5       204          2020-01-01 00:00:00    2021-12-31 23:59:59

And this is my SQL Query, which is from another Solution in this Plattform where i made some requirements changes.

SELECT DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) AS GAP_FROM, DATE_SUB(DATE_FROM,INTERVAL 1 SECOND) AS GAP_TILL
FROM
    (
        SELECT DISTINCT DATE_FROM, ROW_NUMBER() OVER (ORDER BY DATE_FROM) RN
        FROM overlappingtable T1
        WHERE
            LOCATION_ID = 193 AND
            NOT EXISTS (
                SELECT *
                FROM overlappingtable T2
                WHERE T1.DATE_FROM > T2.DATE_FROM AND T1.DATE_FROM < T2.DATE_TILL
            )
        ) T1
    JOIN (
        SELECT DISTINCT DATE_TILL, ROW_NUMBER() OVER (ORDER BY DATE_TILL) RN
        FROM overlappingtable T1
        WHERE
                LOCATION_ID = 193 AND
            NOT EXISTS (
                SELECT *
                FROM overlappingtable T2
                WHERE T1.DATE_TILL > T2.DATE_FROM AND T1.DATE_TILL < T2.DATE_TILL
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
   DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) < DATE_FROM

This Query delivers me this result:

GAP_FROM              GAP_TILL
2020-01-01 00:00:00   2020-01-31 23:59:59

Which is great, this is the free Interval that i have to deliver between entries that have their ranges and dont overlap. But I want to set in this Query two Parameters for The Main Range for this entries. One for the startdate and the other for enddate. For this example:

startdate = '2019-01-01 00:00:00'
enddate = '9999-12-31 23:59:59'

For LOCATION_ID = 193 i am missing the gap between the startdate('2019-01-01 00:00:00') and the first DATE_FROM for the first entry('2019-02-01 00:00:00').

The result that i would like to deliver should look like this for LOCATION_ID = 193:

GAP_FROM              GAP_TILL
2019-01-01 00:00:00   2019-01-31 23:59:59
2020-01-01 00:00:00   2020-01-31 23:59:59
2023-01-01 00:00:00   9999-12-31 23:59:59

Im really new at SQL and could understand this Query, but i can't develop this further to set these Main Ranges and deliver the missing gaps.

Thanks in Advance

Answer 1

You could join to a sub-query with the start & end datetimes.

Then compare to the previous & next datetimes per location_id.

The previous or next datetimes can be found via the LAG & LEAD functions.

WITH CTE_UNDERLAPS AS 
(
  SELECT t.*
  , LAG(DATE_TILL)  OVER (PARTITION BY LOCATION_ID ORDER BY DATE_FROM, DATE_TILL) AS PREV_DATE_TILL
  , LEAD(DATE_FROM) OVER (PARTITION BY LOCATION_ID ORDER BY DATE_FROM, DATE_TILL) AS NEXT_DATE_FROM
  , l.*
  FROM overlappingtable t
  JOIN (
    SELECT 
      CAST('2019-01-01 00:00:00' AS DATETIME) AS START_DATETIME
    , CAST('9999-12-31 23:59:59' AS DATETIME) AS END_DATETIME
  ) l ON DATE_FROM >= START_DATETIME AND DATE_TILL <= END_DATETIME
) 
SELECT LOCATION_ID
, COALESCE(DATE_ADD(PREV_DATE_TILL,INTERVAL 1 SECOND), START_DATETIME) AS DATE_FROM
, DATE_SUB(DATE_FROM,INTERVAL 1 SECOND) AS DATE_TILL
FROM CTE_UNDERLAPS
WHERE COALESCE(DATE_ADD(PREV_DATE_TILL,INTERVAL 1 SECOND), START_DATETIME) < DATE_FROM
UNION
SELECT LOCATION_ID
, DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) AS DATE_FROM
, COALESCE(DATE_SUB(NEXT_DATE_FROM,INTERVAL 1 SECOND), END_DATETIME) AS DATE_TILL
FROM CTE_UNDERLAPS
WHERE DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) < COALESCE(NEXT_DATE_FROM, END_DATETIME)

ORDER BY LOCATION_ID, DATE_FROM, DATE_TILL

LOCATION_ID	DATE_FROM	DATE_TILL
193	2019-01-01 00:00:00	2019-01-31 23:59:59
193	2020-01-01 00:00:00	2020-01-31 23:59:59
193	2023-01-01 00:00:00	9999-12-31 23:59:59
204	2019-01-01 00:00:00	2019-12-31 23:59:59
204	2022-01-01 00:00:00	9999-12-31 23:59:59

Demo on db<>fiddle here