如何在SQLServer中比较前一行的日期和更新条目

ubby3x7f 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(335)

我一直在想办法通过比较前几行的日期来更新一些条目。
以下是我愿意更新的表中存储的日期示例：

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')

提前谢谢！
干杯，

sql sql-server ssms ssms-2016

来源：https://stackoverflow.com/questions/61941316/how-to-compare-dates-from-previous-rows-and-update-entries-in-sql-server

2条答案

按热度按时间

dm7nw8vv1#

我认为可更新的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;

这是一把小提琴

赞(0）回复(0）举报 2021-08-13

ddarikpa2#

你可以试试这个

update Test set DocumentState = 36
where exists (
    select * from Test inrTest
    where inrTest.ContractId < Test.ContractId
    and inrTest.ActualCloseDate > Test.DealsDate
)

如果订单不在“contracid”上，那么只需将contract id条件更改为您要订购的列

赞(0）回复(0）举报 2021-08-13

我来回答

如何在SQLServer中比较前一行的日期和更新条目

2条答案

相关问题

热门标签

最新问答