Update value based on value in previous row

user_15626018 0
Viewed 1424

I have the following table:

CREATE TABLE dbo.Persons 
(
     [Date] date null, 
     [PersonId] int null, 
     [Amount] int null, 
     [Value] int null
)

And here's some sample data:

INSERT INTO dbo.Persons ([Date], [PersonId], [Amount], [Value])
VALUES
('2020-01-01', 1, 200, NULL),
('2020-01-02', 1, 300, NULL),
('2020-01-03', 1, 400, NULL),
('2020-01-04', 1, 500, NULL),
('2020-01-01', 2, 200, NULL),
('2020-01-02', 2, 300, NULL),
('2020-01-03', 2, 400, NULL),
('2020-01-04', 2, 500, NULL),
('2020-01-01', 3, 0, NULL),
('2020-01-02', 3, 0, NULL),
('2020-01-03', 3, 0, NULL),
('2020-01-04', 3, 0, NULL)

My goal: update all of the values in the [Value] column based on values in other columns AND the previous value in [Value] column. Primary key is date+personId.

Explained in pseudo code, my logic needs to something like:

CASE 
    WHEN [Amount] > 200 AND previous row value IN [Value] = 1, then 2
    WHEN [Amount] > 500 AND previous row value in [Value] = 2, then 3
    WHEN [Date] > '2020-01-01' AND [Amount] = 500 AND previous row value in [Value] = 2, then 4

and so on - this captures the general T-SQL code logic..

I only want to update the [Value] column where [Value] is NULL also.

What is the play here? I've read about using LAG, While LOOP, Recursive CTE, etc., but unsure where to go.

recursion sql-update tsql sql-server
2 Answers

You can use lag to reference the previous row's value. In your example data value would remain null though as your case logic depends on the previous row which is null to start.

However, this is how you might approach it with an updatable CTE

with u as (
    select [Date], PersonId,
    case
        when Amount > 200 and Lag(Value) over(partition by PersonId order by [Date]) = 1 then 2
        when Amount > 500 and Lag(Value) over(partition by PersonId order by [Date]) = 2 then 3
        when [Date] > '20200101' and Amount = 500 and Lag(Value) over(partition by PersonId order by [Date])= 2 then 4
    end NewValue
    from Persons
)
update u set Value = NewValue;
user_153326500

You can add a row number and then self-join. You can extend this idea to Row-2, Row-3, etc.

With Qry1 (
    Select  -- Columns from Person table
            , ROWNUMBER() OVER(PARTITION BY PersonId ORDER BY Date) As Seq
    FROM Persons
)
Select  -- Columns from ThisRow
        -- Columns from PrevRow (values will be null if there is
        -- no previous row for this PersonId and Date)
FROM Qry1 ThisRow
LEFT JOIN Qry1 PrevRow
ON ThisRow.PersonId = PrevRow.PersonId
AND ThisRow.Date = PrevRow.Date
AND ThisRow.Seq - 1 = PrevRow.Seq
user_38170680
Related