SQL Find Missing Date Ranges

Question

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

eg.

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

I have a table that shows date ranges from different datasets

eg.

 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 to compare two tables to show which days are missing in the DataSet for this particular month.

eg. for my example above where the dataset is webshopmissing a date range 09/09/2013 - 09/19/2013

Thanks for any help!

Answer 1

You can use CTE and write a query like:

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

Answer 2

If you use the main table

#Temp(Title varchar(10),DateRange date)

You can do something like

CREATE TABLE #ALLDATE(Date1 date)
DECLARE @startDate DATE='9/1/2013'
DECLARE @endDate DATE='9/30/2013'

insert into #ALLDATE
SELECT [Date] = DATEADD(Day,Number,@startDate) 
FROM  master..spt_values 
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate


select 'webshop',Date1 
from #ALLDATE
where Date1 not in 
        (select DateRange from #Temp where Title='webshop' and MONTH(GETDATE())=9)