By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Test (ContractId int, PartnerId int, DocumentState int, DealsDate 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')
6 rows affected
select *
from test
| ContractId | PartnerId | DocumentState | DealsDate | ActualCloseDate |
|---|---|---|---|---|
| 119577922 | 1450216 | 38 | 2016-04-21 00:00:00.000 | 2017-08-01 00:00:00.000 |
| 222138372 | 1450216 | 38 | 2017-11-22 00:00:00.000 | 2019-04-01 00:00:00.000 |
| 223328932 | 1450216 | 38 | 2018-07-30 00:00:00.000 | 2018-11-19 00:00:00.000 |
| 224263667 | 1450216 | 38 | 2019-01-15 00:00:00.000 | 2019-04-19 00:00:00.000 |
| 225286013 | 1450216 | 38 | 2019-06-21 00:00:00.000 | 2019-07-19 00:00:00.000 |
| 225704493 | 1450216 | 38 | 2019-08-30 00:00:00.000 | 2019-12-11 00:00:00.000 |
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;
2 rows affected
select *
from test
| ContractId | PartnerId | DocumentState | DealsDate | ActualCloseDate |
|---|---|---|---|---|
| 119577922 | 1450216 | 38 | 2016-04-21 00:00:00.000 | 2017-08-01 00:00:00.000 |
| 222138372 | 1450216 | 38 | 2017-11-22 00:00:00.000 | 2019-04-01 00:00:00.000 |
| 223328932 | 1450216 | 36 | 2018-07-30 00:00:00.000 | 2018-11-19 00:00:00.000 |
| 224263667 | 1450216 | 36 | 2019-01-15 00:00:00.000 | 2019-04-19 00:00:00.000 |
| 225286013 | 1450216 | 38 | 2019-06-21 00:00:00.000 | 2019-07-19 00:00:00.000 |
| 225704493 | 1450216 | 38 | 2019-08-30 00:00:00.000 | 2019-12-11 00:00:00.000 |