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?

  • Convert this table to a form (date-weight) where weight is 1 for start and -1 for end. Then calculate cumulative sum for a weight. The value where this sum is zero is a start of a gap. But if date ranges cannot overlap then the gap is a range between end and the lead of start. And add fake rows with end=last day of prev. moths, and with start=1 of next month. – Akina Jun 25 '20 at 10:13
  • 1
    Knowing the exact version and edition of SQL Server will help. – Michael Green Jun 25 '20 at 10:16

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

-- 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
      ON  r.d >= t.StartDate 
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
SELECT MIN(d), MAX(d) -- for each island, grab the start and end
  FROM islands 
  GROUP BY island 

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy