Update row using value from row before

Question

i'm looking for update row using value from row before.

I have something like this:

Group by	Value1	Value2	Value2 - expected result
1	0	20	20
1	3	x	23
1	5	x	28
1	2	x	30
2	0	30	30
2	5	x	35
2	2	x	37

Value2 = Value2 from row before + value 1 but column "Group by" is importand. If Value2 before is in another group then: Value2 = Value2

Can sameone explain mi how do this update statement? I tried using CTE with LAG function but i always fall in infinite loop.

Code for create table:
create table test
(
  [GroupBy] int
, [Date] date
, [Value1] int
, [Value2] int
)

Inserting data:

    INSERT INTO test ([GroupBy], [Date] [Value1], [Value2])
VALUES 
(1, '2022-01-01', 0, 20),
(1, '2022-01-02', 3, NULL),
(1, '2022-01-03', 5, NULL),
(1, '2022-01-04', 2, NULL),
(2, '2022-01-01', 0, 30),
(2, '2022-01-02', 5, NULL),
(2, '2022-01-03', 2, NULL)

Primary key by: [GroupBy], [Date]

It could be faster if you did your part and provide queries to create the table and insert the sample data, instead of stories (description) about the table and data `:-)` — Ronen Ariely, Mar 21 '22 at 07:33
What is the order of the rows which define what come before what?!? Remember that table is a set of unordered rows. You must have a column(s) which define the order in the query. Without this information you question cannot be solve as any answer will be nondeterministic (might return different SET in each execution) — Ronen Ariely, Mar 21 '22 at 07:39
Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) . In particular this answer https://stackoverflow.com/a/10309947/8895292 — SOS, Mar 21 '22 at 09:10
I added expected result in last column. You know that is it possible do this using @variable but i want to know did using CTE with LAG will by more optimal for CPU of my server — Boker, Mar 21 '22 at 09:17
(Edit) You shouldn't need LAG. A partitioned `SUM() OVER(....)` should do the trick https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=957ff32dcf7b38c3591158053407ebc8 . Voting to close since there are numerous threads on running totals already — SOS, Mar 21 '22 at 09:28

Ronen Ariely · Accepted Answer · 2022-03-21T09:49:53.087

0

Check using LAG function

-- Using LAG function
-- https://learn.microsoft.com/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699
;With MyCTE as (
    SELECT  
        t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0), 
        PreValue2 = ISNULL(LAG(t.Value2,1,0) OVER (PARTITION BY t.GroupBy ORDER BY [date]),0)
    FROM test t
)
SELECT [date], GroupBy, Value1, Value2, PreValue2, [Value1+PreValue2] = Value1+PreValue2
FROM MyCTE

Following the comment, maybe the original request was not well describe and what you need is not "Value2 from row before + value 1" but "SUM of all Value2 from all rows before + value 1"

In this case, check this solution

-- Value1 + total of all previous Value2
;With MyCTE as (
    SELECT  
        t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0) 
        ,TotalPreValue2 = SUM(ISNULL(t.Value2,0)) 
            OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
    FROM test t
)
SELECT [date], GroupBy, Value1, Value2, TotalPreValue2, [Value1+TotalPreValue2] = Value1+TotalPreValue2
FROM MyCTE
GO

And if you need something else like "sum of all Value2 from ALL previous rows before + sum of all value1 from, previous rows" then check this

--  total of all previous Value1 + total of all previous Value2
;With MyCTE as (
    SELECT  
        t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0) 
        ,TotalPreValue2 = SUM(ISNULL(t.Value2,0)) 
            OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
        ,TotalPreValue1 = SUM(ISNULL(t.Value1,0)) 
            OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
    FROM test t
)
SELECT [date], GroupBy, Value1, Value2, TotalPreValue2, TotalPreValue1, [TotalPreValue1+TotalPreValue2] = TotalPreValue1+TotalPreValue2
FROM MyCTE

edited Mar 21 '22 at 09:49

answered Mar 21 '22 at 07:57

Ronen Ariely

2,336
12
21

But it's not correct result. For row 3 result shoult be 28, for row 4: 30, for row 7: 37 – Boker Mar 21 '22 at 08:50
Why 28? Please explain since this result fits your description in the question. Row 3 get value of previous value2 which is 0 plus the value of value1 which is 5 and 0+5 = 5, so what's wrong?!? – Ronen Ariely Mar 21 '22 at 09:29
This is your request: Value2 from row before + value 1 group by "groupBy" and this is exactly the result my query returns. Please explain how you get to 28. Maybe you mean different result: Value2 from *ALL THE ROWS* before + value 1. this is totally different request and yet the query will be almost the same - please clarify what you need better – Ronen Ariely Mar 21 '22 at 09:32
I added two more options for different requirements. Please check and if these not fit then you need to describe the exact expected result set and the description of how you got these results – Ronen Ariely Mar 21 '22 at 09:51
But i want update column Value2. I mean that value of Value2 is recursion For row2: Value2 = Value2[before] + Value1 = 20 + 3 = 23 --now in row2 Value2 = 23 - this is value using for update in row3 For row3: Value2 = Value2[before] + Value1 = 23 + 5 = 28 -- now in row2 Value2 = 28 etc. – Boker Mar 21 '22 at 09:52
Check my two addition of options. These are different request but the solution is almost the same. Please check and if these not fit then you need to describe the exact FULL expected result set and the description of how you got these results – Ronen Ariely Mar 21 '22 at 09:54
Option 2 is what i need. Thanks! – Boker Mar 21 '22 at 09:54
You are most welcome `:-)` – Ronen Ariely Mar 21 '22 at 09:54

Update row using value from row before

1 Answers1