sql server - SQL Find missing date ranges

Question

I have a table that holds all the days/months of the year

E.G.

Day      Month
1         9
2         9
3         9
4         9
5         9
6         9
7         9
...       ...

I have a table which shows the date ranges from different datasets

E.G.

 DataSet    DateRange
webshop 2013-09-20
webshop 2013-09-21
webshop 2013-09-22
webshop 2013-09-23
webshop 2013-09-24
webshop 2013-09-25
webshop 2013-09-26
webshop 2013-09-27
webshop 2013-09-28
webshop 2013-09-29
webshop 2013-09-30

How would I compare the two tables to show which days are missing from the DataSet for that particular month

E.G. for my example above where the dataset is webshop it is missing the date range 01/09/2013 - 19/09/2013

Thanks for any help!

Answer 1

You can use CTE and write a query as:

declare @StartDate DATE, @EndDate DATE
set @StartDate = '2013-09-01';
set @EndDate = '2013-09-30';

  WITH DateRange(Date) AS
     (
         SELECT
             @StartDate Date
         UNION ALL
         SELECT
             DATEADD(day, 1, Date) Date
         FROM
             DateRange
         WHERE
             Date < @EndDate
     )

     SELECT 'webshop',Date 
     FROM DateRange
     EXCEPT 
     SELECT DataSet,DateRange
     FROM ImportedDateRange
     WHERE DataSet='webshop'
     --You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
     OPTION (MaxRecursion 10000);