我一直在努力，试图通过比较前几行的日期来找到更新某些条目的方法。
这是我愿意更新的表中存储的日期的示例：
ContractId PartnerId DocumentState DealsDate ActualCloseDate
-------------------------------------------------------------------
119577922 1450216 38 2016-04-21 2017-08-01
222138372 1450216 38 2017-11-22 2019-04-01
223328932 1450216 38 2018-07-30 2018-11-19
224263667 1450216 38 2019-01-15 2019-04-19
225286013 1450216 38 2019-06-21 2019-07-19
225704493 1450216 38 2019-08-30 2019-12-11
目的是将DocumentState所有ContractIdsActualCloseDate之前的所有项大于的36更改为36 DealsDate。
输出应如下所示：
ContractId PartnerId DocumentState DealsDate ActualCloseDate
-------------------------------------------------------------------
119577922 1450216 38 2016-04-21 2017-08-01
222138372 1450216 38 2017-11-22 2019-04-01
223328932 1450216 36 2018-07-30 2018-11-19
224263667 1450216 36 2019-01-15 2019-04-19
225286013 1450216 38 2019-06-21 2019-07-19
225704493 1450216 38 2019-08-30 2019-12-11
以下是用于将数据插入临时表的代码。
create table #Test
(
ContractId int,
PartnerId int,
DocumentState int,
DeasDate datetime,
ActualCloseDate datetime
)
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (119577922, 1450216, 38, '2016-04-21', 2017-08-01')
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (222138372, 1450216, 38, '2017-11-22', 2019-04-01')
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (223328932, 1450216, 38, '2018-07-30', 2018-11-19')
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (224263667, 1450216, 38, '2019-01-15', 2019-04-19')
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (225286013, 1450216, 38, '2019-06-21', 2019-07-19')
insert into #Test (ContractId, PartnerId, DocumentState, DealsDate, ActualCloseDate) values (225704493, 1450216, 38, '2019-08-30', 2019-12-11')
提前致谢！
干杯，
我认为可更新的CTE可以满足您的需求：
with toupdate as (
select t.*,
max(ActualCloseDate) over (partition by PartnerId
order by dealsDate
rows between unbounded preceding and 1 preceding
) as prev_max_acd
from #test t
)
update toupdate
set documentstate = 36
where prev_max_acd > dealsdate;
这是一个db <>小提琴
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句