sql server 2008 r2-sql差距重叠日期范围

Question

我有一个查询，用于识别sql server 2008 r2中日期范围的差距和重叠。每个唯一的数据集有12条记录。我想做的是调整或添加到识别间隙和重叠的代码中，并将记录更新为连续的。

--gaps and overlaps tbl_volumes
with s as
(
select esiid,Read_Start,Read_End ,row_number() over(partition by esiid order by Read_Start) rn
from tbl_Volumes
where Status=0
group by esiid,Read_Start,Read_End)
select a.esiid, a.Read_Start, a.Read_End, b.Read_Start as nextstartdate,datediff(d,a.Read_End, b.Read_Start) as gap
into #go
from s a
join s b on b.esiid = a.esiid and b.rn = a.rn + 1
where datediff(d, a.Read_End, b.Read_Start) not in (0,1)
order by a.esiid

以下是我希望看到的不良记录集：

e                   Read_Start  Read_End    Source
10032789402145965   2011-01-21  2011-02-22  867_03_1563303
10032789402145965   2011-02-22  2011-03-21  867_03_1665865
10032789402145965   2011-03-26  2011-04-20  867_03_1782993

Answer 1

好吧，您可以根据下一个值为每条记录分配一个新的Read_end。新启动的计算可以这样进行：

select t.*,
       (select top 1 Read_Start
        from t t2
        where t2.e = t.e and t2.Read_Start > t.Read_Start
        order by t2.Read_Start
       ) as New_Read_End
from t

你真的想更新这个值还是只是看看它应该是什么？