Updating total using values from previous row - SQL Server 2016 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
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.