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