SQL Query Time Coverage

Question

I don´t now if this is the right place for such a question, but I´ll try it :)

I have got several sql db entries which represents time periods

E.G.

Datefrom        datetill
2010-10-01      2011-01-01
2011-02-01      2011-05-16
2011-08-08      2011-09-01

I need to test if a given time period ist fully covered from the entries.

Is there a smart way to figure that out? My first idea was to check the min date and the max date, but how about the "holes" in the time periods?

Maybe there are some SQL Pros that are willing to help me...

Excuse my english, it´s nt my mother language.

Answer 1

Here's one way.

DECLARE @startdate DATE = '2010-10-01'
DECLARE @enddate DATE   = '2011-01-02';

WITH FullRange AS
(
    SELECT @startdate AS [DATE]
    UNION ALL
    SELECT DATEADD(d,1,[DATE])
    FROM FullRange
    WHERE [DATE] < @enddate
),
 DatesTable AS
(
SELECT CAST('2010-10-01' AS DATE) Datefrom, CAST('2011-01-01' AS DATE) datetill UNION ALL
SELECT CAST('2011-02-01' AS DATE) Datefrom, CAST('2011-05-16' AS DATE) datetill UNION ALL
SELECT CAST('2011-08-08' AS DATE) Datefrom, CAST('2011-09-01' AS DATE) datetill
)
/*Return any dates in the range that are unmatched*/
SELECT FullRange.[DATE]
FROM FullRange 
WHERE NOT EXISTS
(SELECT * FROM DatesTable WHERE [DATE] BETWEEN Datefrom AND datetill)
OPTION (MAXRECURSION 0)