SQL Find missing date ranges

sql, sql-server

Solution

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);

Problem

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!

Original source