Updating total using values from previous row - SQL Server 2016

Question

I have a table that depends on the previous row’s values for its new total. We have discovered that a couple of years ago a value was incorrectly altered and has subsequently made all following values incorrect. I have inherited a formula that uses the previous date to calculate but there appears to be dates missing in the table and at this point the formula stops. I would like to make the update use the previous row rather than the date. I have tried using LAG but had issues using it with an UPDATE.

This is an example of the type of data I am using

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20

I need to add Value1 + Value2 + Value3 * Previous Day Total. The answer to this will update the following row's total. As mentioned I need to use an UPDATE because there is existing data in the total, but it's currently incorrect.

The results I would like to achieve are in the Table below. Please be aware that dates are not sequential so I can't use date as a condition. An answer with LAG would be great.

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20
|20151114 |  2   |  3   |  1   | 140
|20151115 |  1   |  1   |  1   | 420
|20151117 |  1   |  3   |  1   | 2100
|20151119 |  2   |  1   |  3   | 12600

Example - for the 20151114 - 2 + 2 + 3 * 20 (Total from the previous row) = 140

Answer 1

I had to do something similar once and had a good result from using a windowing function like below:

SELECT RunningTotal = History.Total + ISNULL((SUM(History.Total) OVER 
                    (PARTITION BY PartitionID 
                    ORDER BY History.EffectiveDate ASC, History.HistoryID ASC 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 0)
FROM   History
ORDER BY History.EffectiveDate, History.HistoryID

This sums up all of the rows before the current row.

You can't update straight from that, but you could put it into a CTE or insert the data into a temp table and then update from one of those.

The performance of it isn't great, but for a one off script in a maintenance window it was good enough for us.

Answer 2

IMHO you must use a cursor due you need to store the previous calculated total.

Note I've added the previous UniqueIdentifierCode because one FOR UPDATE cursor requires a primary key.

CREATE TABLE Hist 
(
    UniqueIdentifierCode VARCHAR(10) PRIMARY KEY,
    EffectiveDate date, 
    Value1 decimal(18,4), 
    Value2 decimal(18,4),
    Value3 decimal(18,4),
    Total decimal(18,4)
);
GO

INSERT INTO Hist VALUES
('N01', '20151112', 2.0, 2.0, 3.0, 20),
('N02', '20151114', 2.0, 3.0, 1.0, 0),
('N03', '20151115', 1.0, 1.0, 1.0, 0),
('N04', '20151117', 1.0, 3.0, 1.0, 0),
('N05', '20151119', 2.0, 1.0, 3.0, 0);

GO

DECLARE @NEW_TOTAL DECIMAL(18,4) = 0;
DECLARE @FIRST BIT = 1;

DECLARE @V1 DECIMAL(18,4),
        @V2 DECIMAL(18,4),
        @V3 DECIMAL(18,4),
        @T DECIMAL(18,4);
        
DECLARE curHist CURSOR FOR
    SELECT Value1,
           Value2,
           Value3,
           Total
    FROM   Hist
    ORDER BY EffectiveDate
    FOR UPDATE OF Total;
    
    
OPEN curHist;
FETCH NEXT FROM curHist INTO @V1, @V2, @V3, @T;

WHILE (@@FETCH_STATUS = 0)
BEGIN

    IF @FIRST = 1
    BEGIN
        SET @NEW_TOTAL = (@V1 + @V2 + @V3) * @T;
    END
    ELSE
    BEGIN
        UPDATE Hist
        SET    Total = @NEW_TOTAL
        WHERE  CURRENT OF curHist;
        
        SET @NEW_TOTAL = (@V1 + @V2 + @V3) * @NEW_TOTAL;
    END
    
    SET @FIRST = 0;
    FETCH NEXT FROM curHist INTO @V1, @V2, @V3, @T;
END

CLOSE curHist;
DEALLOCATE curHist;

SELECT * FROM Hist;

GO

UniqueIdentifierCode | EffectiveDate       | Value1 | Value2 | Value3 | Total     
:------------------- | :------------------ | :----- | :----- | :----- | :---------
N01                  | 12/11/2015 00:00:00 | 2.0000 | 2.0000 | 3.0000 | 20.0000   
N02                  | 14/11/2015 00:00:00 | 2.0000 | 3.0000 | 1.0000 | 140.0000  
N03                  | 15/11/2015 00:00:00 | 1.0000 | 1.0000 | 1.0000 | 840.0000  
N04                  | 17/11/2015 00:00:00 | 1.0000 | 3.0000 | 1.0000 | 2520.0000 
N05                  | 19/11/2015 00:00:00 | 2.0000 | 1.0000 | 3.0000 | 12600.0000

dbfiddle here