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.

score 3 · Accepted Answer · answered Oct 02 '09 at 03:08

3

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.

answered Oct 02 '09 at 03:08

Aaron Alton

22,728
6
34
32

Actually the solution i am looking for is in the link you provided - 1st comment by Dennis Allen. – AngryHacker Oct 02 '09 at 03:44

score -1 · Answer 2 · answered Oct 02 '09 at 03:10

-1

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.

answered Oct 02 '09 at 03:10

Noon Silk

54,084
6
88
105

Note to people reading: unless someone provides a good reason for this downvote, it's important to note that I'm not wrong - Triggers are far better than cursors for this purpose, and maintaining a balance per-transaction-entry is a little strange. – Noon Silk Oct 02 '09 at 04:28
I didn't downvote it, but your approach is wrong. Note that the table is temporary. Why would I do a behind the scenes thing with the triggers when one can happily do it in a script. – AngryHacker Oct 02 '09 at 21:03
I didn't notice it was temp; you do it with triggers so you always have it up to date. Then you when you want to run a query over a given range, you just do that (cause you have historical data). Your approach makes it difficult to, say, insert a new row (it'd for a total recalculation) at given point. – Noon Silk Oct 02 '09 at 23:53

How to update running balance in one shot?

2 Answers2