How to update running balance in one shot?

Question

I have a temp table, let's call it #invoices, defined as

create table (id int identity(1, 1), billed money, credited money, balance money)

i have the following data in it

Billed   Credited
140.00   
20.00
          60.00
          20.00
         -20.00

I would like to update the balance column with the running balance. so that the Balance column is updated properly. Balance is basically, Billed - Credited, but has to take into the account the previous row.

So in my example, the Balance will be so:

Billed   Credited  Balance
140.00              140.00
20.00               160.00
          60.00     100.00
          20.00      80.00
         -20.00    -100.00

Is there a way to do this without looping through the rows and keeping the running balance? Basically I am looking to update the Balance column in a set-based way.

Answer 1

There are set-based ways to calculate running totals in SQL Server, however in the current versions of SQL Server a cursor-based solution is often quicker.

Adam Machanic wrote a great article on it here.

Answer 2

The answer is triggers. I use them, and it works beautifully. I don't have exactly your setup (it's slightly strange, if I may say), but in general, they are the correct approach here. You will need to be mindful of ordering, but other than that, it should be fine.