生成缺失的日期 + Sql Server（基于集）

Question

我有以下

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

如何生成与每个 eventid 有关的缺失日期？

编辑： 缺失的间隙需要根据eventid来查找。例如，对于 eventid 1，输出应为 1/3/2009、1/4/2009、1/5/2009.. 对于 eventtype id 2，输出应为 1/5/2009、1/6/2009... 到 1 /9/2009 等

我的任务是找出两个给定日期之间缺失的日期。

这是我到目前为止所做的全部事情

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'

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

理想的输出将

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

是它必须是 SET BASED 并且开始和结束日期不应该被硬编码

提前致谢

Answer 1

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

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

Answer 2

像 rexem - 我创建了一个包含类似 CTE 的函数来生成您需要的任何一系列日期时间间隔。像您一样按日期时间间隔汇总数据非常方便。
更详细的帖子和函数源代码位于：

在没有查询的返回结果中插入日期

一旦你有了“按日期排列的事件计数”...你丢失的日期将是计数为 0 的日期。