我一直在想办法通过比较前几行的日期来更新一些条目。
以下是我愿意更新的表中存储的日期示例:
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
到36岁 ContractId
是为了什么 ActualCloseDate
之前的任何条目的 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')
提前谢谢!
干杯,
2条答案
按热度按时间dm7nw8vv1#
我认为可更新的cte可以满足您的需求:
这是一把小提琴
ddarikpa2#
你可以试试这个
如果订单不在“contracid”上,那么只需将contract id条件更改为您要订购的列