How to compute running totals in SQL using data from preceding records

Data and Databases
1

I’m trying to create a SQL query that transforms my source data into a cumulative calculation format. My original table has timestamp info, category types, base amounts, and change values. I need to calculate running totals where each row’s new amount becomes the starting point for the next calculation.

Source Data:

DateTime_ID Category Base_Amount Movement_Delta
2021092414 D 16923 63
2021092415 D 0 149
2021092415 D 0 -56
2021092415 D 0 -131
2021092415 D 0 -79

Expected Result:

DateTime_ID Category Current_Amount Updated_Amount
2021092414 D 16923 16986
2021092415 D 16986 17135
2021092415 D 17135 17079
2021092415 D 17079 16948
2021092415 D 16948 16869

I tried using LAG window functions but couldn’t get the cumulative effect working properly. The category field can have values other than ‘D’ as well. How can I achieve this running calculation across rows?

2

You’re right - Current_Amount needs to pull from the previous row’s Updated_Amount, not the original Base_Amount. I’ve hit this exact problem building financial reports. Window functions won’t cut it here since each calculation depends on the result from the row above. Use a recursive CTE instead. Set up your first CTE to grab the initial row for each category, then UNION ALL to build each following row by joining on the previous Updated_Amount. That way the cumulative effect flows through all categories, not just category D.

3

you can do it with SUM and window functions. like, try SUM(Movement_Delta) OVER (PARTITION BY Category ORDER BY DateTime_ID ROWS UNBOUNDED PRECEDING) for those running totals. just make sure to get the current_amount from the last updated amount.

4

Interesting challenge! What about trying a self-join? What’s your table structure for the other category values - do you need separate running totals for each or can they be combined? Also, how do you handle ordering when multiple rows have the same timestamp?