MSSQL – Running total

ID/date_start/payment
1 2022-02-07 10:18:19.000 1.00
2 2022-02-07 10:18:19.000 2.00
3 2022-02-07 10:18:19.000 3.00
4 2022-02-07 10:18:19.000 4.00
5 2022-02-07 10:18:19.000 5.00
6 2022-02-06 10:18:19.000 6.00
7 2022-02-06 10:18:19.000 7.00
8 2022-02-06 10:18:19.000 8.00
9 2022-02-06 10:18:19.000 9.00
10 2022-02-05 10:18:19.000 10.00

SELECT a.date_start, a.payment,
    (SELECT SUM(payment) as payment
        FROM (SELECT x.date_start, SUM(x.payment) as payment FROM tmp_test as x GROUP BY x.date_start) b
        WHERE b.date_start <= a.date_start
) AS b
FROM (SELECT y.date_start,SUM(y.payment) as payment FROM tmp_test as y GROUP BY y.date_start) a

date_start/payment/b
2022-02-05 10:18:19.000 10.00 10.00
2022-02-06 10:18:19.000 30.00 40.00
2022-02-07 10:18:19.000 15.00 55.00

see test data @ https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server/10309947

This entry was posted on Monday, February 7th, 2022 at 10:37 amand is filed under MSSQL Database Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.