生成缺失日期+ Sql Server(基于设置)

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

编辑:缺失的空白将根据eventid找到。例如eventid 1的输出应是1/3/2009,1/4/2009,1/5/2009。对于eventtype id 2将是1/5/2009,1/6/2009…1/9/2009等

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

实际上我要做的是，我已经生成了一个日历表，从那里我试图根据id找出丢失的日期

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

由于adavnce

2 个解决方案

#1

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

以下使用递归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.

它使用DATEADD函数生成日期。可以更改为以开始日期和结束日期为参数。根据KM的评论，它比使用数字表技巧要快。

#2

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:

像rexem一样——我创建了一个包含类似CTE的函数来生成任何你需要的时间间隔序列。非常方便地以时间间隔总结数据，就像您正在做的那样。更详细的文章和功能源代码如下:

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.

一旦你有了“事件按日期计数”……你错过的日期将是那些计数为0的日期。

#1