Q:

Determine Days Not Covered Based on Fields Containing "From" and "To" Dates

Server: Microsoft SQL Server
SQLFiddle: http://www.sqlfiddle.com/#!18/cdfa3/1/0
If I have rows containing a "start" date and an "end date", how can write a SQL query that will list the days that are not contained between those dates.
Example (see SQLFiddle link above for a playable demo):
startdate                  enddate
2019-06-06 00:00:00.000    2019-06-08 00:00:00.000
2019-06-10 00:00:00.000    2019-06-11 00:00:00.000
2019-06-12 00:00:00.000    2019-06-13 00:00:00.000

We have a coverage gap on June 9th, because we have coverage from June 6th-June 8th, then on June 10th-June 13th. 
How is it possible to identify the date of June 9th as having no coverage based on rows that have date ranges?

A:

You could use generated calendar table and LEFT JOIN:
DECLARE @min DATE, @max DATE;
SELECT @min = MIN(workingdatestart), @max = MAX(workingdateend) FROM workingdates;

WITH cte AS (
  SELECT DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY 1/0), @min) AS d
  FROM sys.objects s, sys.objects s2
)
SELECT c.d AS gap
FROM cte c
LEFT JOIN workingdates w ON c.d BETWEEN w.workingdatestart and w.workingdateend
WHERE c.d < @max AND w.workingDateId IS NULL;

db<>fiddle demo

