Generate missing dates + Sql Server (SET BASED)

Question

I have the following

id eventid  startdate enddate

1 1     2009-01-03 2009-01-05
1 2     2009-01-05 2009-01-09
1 3     2009-01-12 2009-01-15

How to generate the missing dates pertaining to every eventid?

Edit: The missing gaps are to be find out based on the eventid's. e.g. for eventid 1 the output should be 1/3/2009,1/4/2009,1/5/2009.. for eventtype id 2 it will be 1/5/2009, 1/6/2009... to 1/9/2009 etc

My task is to find out the missing dates between two given dates.

Here is the whole thing which i have done so far

declare @tblRegistration table(id int primary key,startdate date,enddate date)
insert into @tblRegistration 
        select 1,'1/1/2009','1/15/2009'
declare @tblEvent table(id int,eventid int primary key,startdate date,enddate date)
insert into @tblEvent 
        select 1,1,'1/3/2009','1/5/2009' union all
        select 1,2,'1/5/2009','1/9/2009' union all
        select 1,3,'1/12/2009','1/15/2009'

;with generateCalender_cte as
(
    select cast((select  startdate from @tblRegistration where id = 1 )as datetime) DateValue
       union all
        select DateValue + 1
        from    generateCalender_cte   
        where   DateValue + 1 <= (select enddate from @tblRegistration where id = 1)
)
select DateValue as missingdates from generateCalender_cte
where DateValue not between '1/3/2009' and '1/5/2009'
and DateValue not between '1/5/2009' and '1/9/2009'
and DateValue not between '1/12/2009'and'1/15/2009'

Actually what I am trying to do is that, I have generated a calender table and from there I am trying to find out the missing dates based on the id's

The ideal output will be

eventid                    missingdates

1             2009-01-01 00:00:00.000

1             2009-01-02 00:00:00.000

3             2009-01-10 00:00:00.000

3            2009-01-11 00:00:00.000

and also it has to be in SET BASED and the start and end dates should not be hardcoded

Thanks in adavnce

What do you mean by "missing dates"? – David Elizondo Nov 02 '09 at 05:50 — David Elizondo, Nov 02 '09 at 05:50

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

The following uses a recursive CTE (SQL Server 2005+):

WITH dates AS (
     SELECT CAST('2009-01-01' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT t.eventid, d.date
  FROM dates d 
  JOIN TABLE t ON d.date BETWEEN t.startdate AND t.enddate

It generates dates using the DATEADD function. It can be altered to take a start & end date as parameters. According to KM's comments, it's faster than using the numbers table trick.

edited May 23 '17 at 12:07

Community

1
1

answered Nov 02 '09 at 06:06

OMG Ponies

325,700
82
523
502

Sir this will give me for 1 event type. How about for the all event types? – priyanka.sarkar Nov 02 '09 at 06:13
Event type? Sorry, but I don't understand what you are referring to. – OMG Ponies Nov 02 '09 at 06:20
Sorry.. possibly I didnot make myself clear.. actually the missing gaps are to be find out based on the eventid's. e.g. for eventid 1 the output should be 1/3/2009,1/4/2009,1/5/2009.. for eventtype id 2 it will be 1/5/2009, 1/6/2009... to 1/9/2009 etc. – priyanka.sarkar Nov 02 '09 at 06:23
That's more clear, but you should update the question so everyone is on the same track. – OMG Ponies Nov 02 '09 at 06:27
sur sir.. it wil do it now itself – priyanka.sarkar Nov 02 '09 at 06:29

score 1 · Answer 2 · edited May 23 '17 at 12:13

Like rexem - I made a function that contains a similar CTE to generate any series of datetime intervals you need. Very handy for summarizing data by datetime intervals like you are doing. A more detailed post and the function source code are here:

Insert Dates in the return from a query where there is none

Once you have the "counts of events by date" ... your missing dates would be the ones with a count of 0.

Generate missing dates + Sql Server (SET BASED)

2 Answers2