[SOLVED] How to compare dates from previous rows and update entries in SQL Server

How to compare dates from previous rows and update entries in SQL Server

I've been pulling my hair out in an attempt to find a way of updating some entries by comparing the dates from previous rows.

Here's a sample of the date stored in the table I am willing to update:

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

The aim is to change the DocumentState to 36 for all the ContractIds for which the ActualCloseDate of any of the entries prior to it is bigger than its DealsDate.

The output should look like this:

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

Below is the code for inserting the data into a temporary table.

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

Thanks in advance!

Cheers,

Solution

I think an updatable CTE does what you want:

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;

Here is a db<>fiddle