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

Answer 1

This looks similar to the question I asked.

Answer 2

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.

Answer 3

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:

http://stackoverflow.com/questions/373490/insert-dates-in-the-return-from-a-query-where-there-is-none/373734#373734

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

Ron