How to calculate a running balance without using cursors

To calculate a running balance, you need to go twice through the data. Once to get the data and the second time for the running balance which is a summary up to that row.

You should never use cursors. Cursors are a very slow operation because everything has to be copied and done though the tempdb database. Microsoft SQL Server does NOT read A ROW. All disk operations are page based. MSSQL reads a page [usually 8k in size], then parses it for reading all the rows/indexes on that page. MSSQL Server, like all other SQL servers, are optimized for set operations and not for operating on each row.

Microsoft SQL Server, like all good SQL servers, allow for subqueries within the select statement. The subquery is what will give us the running balance.

Data used

  1. USE sql911;
  2. go
  3. SELECT counter,emp_short,trans_date,debit,credit
  4. FROM trans02;
  5. go
counter     emp_short trans_date              debit                 credit
----------- --------- ----------------------- --------------------- ---------------------
10          000190    2007-06-01 00:00:00.000 NULL                  125.00
20          000110    2007-06-01 00:00:00.000 NULL                  100.00
30          000190    2007-06-02 00:00:00.000 25.00                 NULL
40          000030    2007-07-15 00:00:00.000 357.00                NULL
50          000030    2007-07-16 00:00:00.000 NULL                  22.00
60          000030    2007-07-18 00:00:00.000 NULL                  41.00
70          000030    2007-07-20 00:00:00.000 NULL                  29.50
80          000110    2007-07-20 00:00:00.000 42.00                 NULL
90          000110    2007-07-22 00:00:00.000 66.00                 NULL
100         000110    2007-07-23 00:00:00.000 77.00                 NULL
110         000010    2007-07-23 00:00:00.000 11.00                 NULL
120         000010    2007-07-24 00:00:00.000 22.56                 NULL
130         000010    2007-07-25 00:00:00.000 33.69                 NULL

(13 row(s) affected)

Calculate running balance

  1. SELECT counter, emp_short, trans_date, debit, credit,
  2.       (SELECT SUM(COALESCE(debit,0) - COALESCE(credit,0))
  3.       FROM trans02 tr2
  4.       WHERE tr2.counter <= tr1.counter and
  5.             tr2.emp_short = tr1.emp_short) AS 'Running balance'
  6. FROM trans02 tr1
  7. ORDER BY emp_short, counter;
  8. go
counter     emp_short trans_date              debit                 credit                Running balance
----------- --------- ----------------------- --------------------- --------------------- ---------------------
110         000010    2007-07-23 00:00:00.000 11.00                 NULL                  11.00
120         000010    2007-07-24 00:00:00.000 22.56                 NULL                  33.56
130         000010    2007-07-25 00:00:00.000 33.69                 NULL                  67.25
40          000030    2007-07-15 00:00:00.000 357.00                NULL                  357.00
50          000030    2007-07-16 00:00:00.000 NULL                  22.00                 335.00
60          000030    2007-07-18 00:00:00.000 NULL                  41.00                 294.00
70          000030    2007-07-20 00:00:00.000 NULL                  29.50                 264.50
20          000110    2007-06-01 00:00:00.000 NULL                  100.00                -100.00
80          000110    2007-07-20 00:00:00.000 42.00                 NULL                  -58.00
90          000110    2007-07-22 00:00:00.000 66.00                 NULL                  8.00
100         000110    2007-07-23 00:00:00.000 77.00                 NULL                  85.00
10          000190    2007-06-01 00:00:00.000 NULL                  125.00                -125.00
30          000190    2007-06-02 00:00:00.000 25.00                 NULL                  -100.00

(13 row(s) affected)
  • Line 2 to 5 is the secret sauce:
    1. Add all the debit minus the credit for the employee of the current row up to the current row.
    2. Line 5: Only look the current employee.
    3. Line 4: Only look at the data up to the current counter. This what give us the rolling balance up to this row.
  • Line 7: Sort by employee then counter, so that the rolling balance will be reset to 0 to start again when we get to the next employee.

Running balance performance

Running balance execution plan
Running balance execution plan
  1. The most expensive operation is the sort!
  2. The original table scan and the sort represent 80% of the cost.
Running balance table scan
Running balance table scan
  1. Although the original trans02 table has only 13 rows, the secondary table scan shows: 29 rows
  2. This is due to the second pass of 13 rows and the reset to null for each employee.