SQL Update column as running balance
Posted on 2014-03-12
I have a table - TRANSACTIONS that has columns AGREEMENTID, TRANSACTIONDATE, OrigInventoryCost, InventoryCostBalance. I need the InventoryCostBalance updated to be a running total of OrigInventoryCost. Right Now, the InventoryCostBalance is all zeroes.
Here is a small sampling of what it should look like.
AgreementID TRANSACTIONDATE OrigInventoryCost InventoryCostBalance (should be)
1 07/09/13 08:00:00 299.99 299.99
1 07/09/13 08:01:00 -25.00 274.99
1 08/09/13 08:00:00 -25.00 249.99
I cannot use a CURSOR as there are upwards of 2 million records now and many added daily. I can handle the daily additions, but cannot seem to get the table updated with current records.
Does anyone know how to get the data updated properly?