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!

ok, so you are going to check the missing dates for one month at a given time? — huMpty duMpty, Dec 19 '13 at 09:24

Deepshikha · Accepted Answer · 2013-12-19T10:18:32.223

10

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

edited Dec 19 '13 at 10:18

answered Dec 19 '13 at 09:29

Deepshikha

9,896
2
21
21

Ah, just one query, i have different datasets,dateranges in the same table, is there any way to specify only check `webshop` or any other datasets as a parameter? – neeko Dec 19 '13 at 09:46
@neeko: Put where condition to filter out only the *webshop* dates – huMpty duMpty Dec 19 '13 at 09:47
@huMptyduMpty thanks again for your help, I'm guessing this would go in the second select statement? – neeko Dec 19 '13 at 09:48
1

@neeko: According to this answer it should be something like `SELECT DataSet,DateRange FROM ImportedDateRange WHERE DataSet='weblog'` – huMpty duMpty Dec 19 '13 at 09:56
Probably not significant for such a small sequence, but just FYI [using recursive CTEs to generate a sequential dataset](http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2) is probably the least efficient way of doing it. – GarethD Dec 19 '13 at 10:38

score 2 · Answer 2 · answered Dec 19 '13 at 10:00

If you main table is

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

You can do like 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)

SQL Find missing date ranges

2 Answers2

Linked