SQL Server 2008:如何更新以前记录的结束日期列
问题描述:
我在下面有一些记录。对于每个客户,只有一条记录可以有END_DATE=12/31/9998
。SQL Server 2008:如何更新以前记录的结束日期列
所以我需要结束日期基于EFFECT_DATE
列以前的记录。
例如,我需要从 12/31/9998
更新ID=2
的END_DATE
到7/17/2017
为CLT_NBR=12375
ID CLT_NBR IS_PRIMARY EFFECT_DATE END_DATE
-----------------------------------------------
1 12375 1 8/13/2015 9/30/2015
2 12375 1 10/1/2015 12/31/9998
3 12375 1 7/18/2017 12/31/9998
4 12331 1 2/3/2016 7/8/2016
5 12331 1 7/9/2016 12/31/9998
答
您可以使用lead
获得下一个生效日期和减去一天来更新当前行的结束日期。
with cte as (select t.*,coalesce(dateadd(day,-1,lead(effect_date) over(partition by clt_nbr order by id),'9998-12-31') as new_end_date
from tbl t)
update cte
set end_date=new_end_date
对于SQL Server 2008,使用
with rownums as (select t.*,row_number() over(partition by clt_nbr order by id) as rnum from tbl t)
,cte as (select r1.*,dateadd(day,-1,coalesce(r2.effect_date,'9999-01-01')) as new_end_date
from rownums r1
left join rownums r2 on r1.clt_nbr=r2.clt_nbr and r1.rnum=r2.rnum-1
)
update cte
set end_date=new_end_date
+0
我正在使用sql server 2008 – Ice
答
基础上加入和将相关数据的解决方案。现在我经常使用CTE,但在它们很受欢迎之前,我们不得不考虑使用这样的东西。
select *
into ##test1
from
(
select ID = 1, CLT_NBR = 12375, IS_PRIMARY = 1, EFFECT_DATE = cast('8/13/2015' as date), END_DATE = cast('9/30/2015' as date)
union all select ID = 2, CLT_NBR = 12375, IS_PRIMARY = 1, EFFECT_DATE = cast('10/1/2015' as date), END_DATE = cast('12/31/9998' as date)
union all select ID = 3, CLT_NBR = 12375, IS_PRIMARY = 1, EFFECT_DATE = cast('7/18/2017' as date), END_DATE = cast('12/31/9998' as date)
union all select ID = 4, CLT_NBR = 12331, IS_PRIMARY = 1, EFFECT_DATE = cast('2/3/2016' as date), END_DATE = cast('7/8/2016' as date)
union all select ID = 5, CLT_NBR = 12331, IS_PRIMARY = 1, EFFECT_DATE = cast('7/9/2016' as date), END_DATE = cast('12/31/9998' as date)
) x
select * from ##test1
select t.ID, t.CLT_NBR, t.IS_PRIMARY, t.EFFECT_DATE, END_DATE = isnull(dateadd(day,-1,min(t_next.EFFECT_DATE)),'9998-12-31')
from ##test1 t
left join ##test1 t_next on t_next.CLT_NBR = t.CLT_NBR and t_next.effect_date > t.effect_date
group by t.ID, t.CLT_NBR, t.IS_PRIMARY, t.EFFECT_DATE
update t
set END_DATE = helper.END_DATE
from ##test1 t
left join
(
select t.ID, t.CLT_NBR, t.IS_PRIMARY, t.EFFECT_DATE, END_DATE = isnull(dateadd(day,-1,min(t_next.EFFECT_DATE)),'9998-12-31')
from ##test1 t
left join ##test1 t_next on t_next.CLT_NBR = t.CLT_NBR and t_next.effect_date > t.effect_date
group by t.ID, t.CLT_NBR, t.IS_PRIMARY, t.EFFECT_DATE
) helper on helper.id = t.id
select * from ##test1
drop table ##test1
答
您可以使用查询类似下面的SQL 2008兼容的答案
CREATE TABLE yourTbl (ID int,CLT_NBR int,IS_PRIMARY int,EFFECT_DATE date, END_DATE date)
INSERT INTO yourTbl VALUES
(1 ,12375,1,'8/13/2015','9/30/2015')
,(2 ,12375,1,'10/1/2015','12/31/9998')
,(3 ,12375,1,'7/18/2017','12/31/9998')
,(4 ,12331,1, '2/3/2016','7/8/2016')
,(5 ,12331,1, '7/9/2016','12/31/9998')
;with y as
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY CLT_NBR ORDER BY EFFECT_DATE DESC) R
FROM yourTbl WHERE END_DATE ='12/31/9998'
)
UPDATE y1
SET y1.END_DATE= y2.EFFECT_DATE
FROM
y y1 JOIN y y2
ON y1.R=y2.R+1 and y1.CLT_NBR=y2.CLT_NBR
select * from yourTbl
为什么不降END_DATE列和使用递归CTE在查询计算它数据? –
你应该看到我的答案为sql 2008兼容版本 – DhruvJoshi
@SeanLange - 尽可能多地我也推荐这个(或者用'LEAD()')的东西,那些查询会消耗大量资源。由于这些数据通常相当静态,所以缓存结束日期通常会更好,尽管可以将其计算为列。也就是说,我绝对建议将它作为**独占**('