SQL Query Time Coverage

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?

May开发者_运维问答be there are some SQL Pros that are willing to help me...

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

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)