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:
