Search code examples
sqlsql-serverlag

SQL Server 2012, update record based on next record

I'm struggling to find a solution to update (not just select) SQL table records based on previous values. I can get the script to populate the immediate subsequent record with the LAG() function, but when I have the same values in a row (like 'CC' below) I can't get it to populate with the next value that's not the same as the current value.

It would also be helpful to be able to add CASE/WHEN condition so that only values with the same BaseID are evaluated. Any help would be greatly appreciated.

Here is my desired result:

BaseID  Value  Date        NextValue
1       AA     2017-10-01  BB
1       BB     2017-10-02  CC
1       CC     2017-10-03  DD
1       CC     2017-10-03  DD
1       CC     2017-10-03  DD
1       DD     2017-10-04  NULL
2       EE     2017-10-01  FF
2       FF     2017-10-02  GG
2       GG     2017-10-03  NULL
Solution

  • Get the distinct baseid,value,date combinations and use lead to get the next value in a cte and use itto update.

    with cte as (select t1.baseid,t1.value,t1.nextvalue,t2.nxt_value
             from tbl t1 
             left join (select t.*,lead(value) over(partition by baseid order by datecol) as nxt_value 
                        from (select distinct baseid,datecol,value from tbl) t
                       ) t2 
             on t1.baseid=t2.baseid and t1.datecol=t2.datecol and t1.value=t2.value
             )
update cte set nextvalue=nxt_value

    This assumes there can't be multiple values for a given baseid,date combination.