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

I have something like this:

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]

CodePudding user response：

Check using

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