代码之家 › 专栏 › 技术社区 › priyanka.sarkar

生成缺少的日期+Sql Server(基于集合)

gaps-and-islands sql-server-2005 tsql sql-server sql

0

priyanka.sarkar · 技术社区 · 16 年前

我有以下几点

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找出缺失的间隙。e、 g.对于eventid 1,输出应为1/3/2009、1/4/2009、1/5/2009。。对于eventtype id 2,它将是1/5/2009、1/6/2009。。。至2009年1月9日等

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

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

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

而且它必须是基于集合的,并且开始和结束日期不应该硬编码

2 回复 | 直到 14 年前

1

3

Community Mohan Dere 9 年前

以下内容使用递归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's comments ,它比使用数字表技巧更快。

2

1

Community Mohan Dere 9 年前

像rexem一样,我创建了一个包含类似CTE的函数,以生成您需要的任何一系列日期时间间隔。非常方便地按日期时间间隔汇总数据,就像您正在做的那样。

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

一旦你有了“日期事件计数”。。。您缺少的日期将是计数为0的日期。