Sql-server – SQL Server – Get the Date Ranges

dategaps-and-islandssql server

I have a table with start date and end date. For example,

PlaceID | StartDate  | EndDate
1       | 2020-01-01 | 2020-01-04
1       | 2020-01-10 | 2020-01-15
2       | 2020-01-03 | 2020-01-06
2       | 2020-01-20 | 2020-01-23

I need to write a script to get the date ranges that is not in the table for each PlaceID. (For now considering the month of January.)

For PlaceID 1 – I need the output date ranges as

2020-01-05 to 2020-01-09
2020-01-16 to 2020-01-31

For PlaceID 2 – I need the output date ranges as

2020-01-01 to 2020-01-02
2020-01-07 to 2020-01-19
2020-01-24 to 2020-01-31

What is the SQL logic behind this that I can use?

Best Answer

This is essentially an islands problem, except you are trying to find all the rows that don't belong to an island (these are gaps, but conceptually I find it easier to think about the islands). Basically, we want to find all of the possible days in your overall range, then identify the days that are "covered" within that range. The end result you want are all of the islands of days that are not covered.

My solution assumes you are on a currently supported version of SQL Server (2012+). Please always use a tag for the minimum version of SQL Server you need to support, since that can change the answer and prevent people from wasting time.

-- sample table + data:
DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200104'),(1,'20200110','20200115'),
(2,'20200103','20200106'),(2,'20200120','20200123');


-- input parameters (place you care about, start and end of date range):
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101', 
        @EndDateOfInterest   date = '20200131';
    
;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d) 
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate 
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT MIN(d), MAX(d) -- for each island, grab the start and end
  FROM islands 
  GROUP BY island 
  ORDER BY MIN(d);

Generating the contiguous range of dates is a topic of its own. I like the recursive CTE option because it's quick and easy for me to remember, and doesn't require any other objects to exist. But it has a quirk: If the range of days could be more than 100, you'll need to add OPTION (MAXRECURSION x) to the end of the statement (where x is the largest number of days you would need to support). If x > 32,767 (89 years or so), x will need to be 0.

If you don't like recursive CTEs, but you already have a calendar table (say, dbo.Calendar with a [date] column), the date_range CTE can simply be:

;WITH date_range(d) AS
(
  SELECT [date] FROM dbo.Calendar
  WHERE [date] >= @StartDateOfInterest
    AND [date] <= @EndDateOfInterest
),

If you don't like recursive CTEs, and don't have a calendar table, but you do have a numbers table (say, dbo.Numbers, with an int column named num), you can do this for the initial CTE:

;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, num-1, @StartDateOfInterest)
  FROM dbo.Numbers
  WHERE num >= 1 ORDER BY num
),

And if you don't like recursive CTEs and you don't have a calendar or numbers table, you can do this:

;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @StartDateOfInterest)
  FROM sys.all_columns
  ORDER BY [object_id]
),

If you don't like recursive CTEs, you don't have a calendar or numbers table, and the caller doesn't have access to catalog views like sys.all_columns, I give up.

More information here:

Related Question