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
– David Elizondo

2009-11-02 05:50:03 +00:00
Commented Nov 2, 2009 at 5:50 — David Elizondo
– David Elizondo, Commented Nov 2, 2009 at 5:50

Community · Accepted Answer · 2017-05-23 12:07:05Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Nov 2, 2009 at 6:06

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

priyanka.sarkar Over a year ago

Sir this will give me for 1 event type. How about for the all event types?

OMG Ponies Over a year ago

Event type? Sorry, but I don't understand what you are referring to.

priyanka.sarkar Over a year ago

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.

OMG Ponies Over a year ago

That's more clear, but you should update the question so everyone is on the same track.

priyanka.sarkar Over a year ago

sur sir.. it wil do it now itself

Community · Accepted Answer · 2017-05-23 12:13:36Z

1

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Nov 2, 2009 at 6:14

Ron Savage

11.1k4 gold badges29 silver badges36 bronze badges

Collectives™ on Stack Overflow

Generate missing dates + Sql Server (SET BASED)

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related