How to do calculations based on a running total with MSSQL

The problem is: Once you have a running balance, how do you do other calculations based on the running balance.
The answer is: Common Table Expression or CTE.

Starting with SQL Server 2005, Microsoft has implemented the Common Table Expression or CTE which is an ANSI SQL-99 standard. It's also implemented by Oracle and IBM DB2.

Common Table Expression

  • It's a temporary result set.
  • It only exists for the duration of the query.
  • Can be applied to:
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • CREATE VIEW

Common Table Expression are used for:

  • Recursive queries.
  • Substitute views.
  • Grouping by a column derived from a sub-query.
  • Use the resulting table many times in the same statement.

Translation: we can use use the common table expression to make calculations based on other calculations or summary operations.

Data used

SELECT counter AS 'Counter',
       emp_short AS 'Employee',
       trans_date AS 'Date',
       trans_type AS 'Transaction',
       amount AS 'Amount'
FROM transactions;
go
Counter     Employee   Date                    Transaction Amount
----------- ---------- ----------------------- ----------- ---------------------
1           000010     2007-05-01 00:00:00     IN          345.00
11          000010     2007-05-02 00:00:00     IN          175.00
21          000010     2007-05-03 00:00:00     OUT         -75.00
31          000010     2007-05-05 00:00:00     OUT         -100.00
41          000030     2007-05-15 00:00:00     IN          10000.00
51          000030     2007-05-16 00:00:00     IN          435.00
61          000030     2007-05-20 00:00:00     OUT         -125.00
71          000030     2007-05-30 00:00:00     OUT         -1100.00
81          000110     2007-04-12 00:00:00     OUT         -250.00
91          000110     2007-04-30 00:00:00     OUT         -75.00
101         000110     2007-05-10 00:00:00     IN          1000.00
111         000190     2007-06-01 00:00:00     IN          99.00
121         000190     2007-06-01 00:00:00     IN          134.00
131         000310     2007-07-01 00:00:00     IN          1156.40
141         000310     2007-07-02 00:00:00     IN          67.00
151         000310     2007-08-01 00:00:00     OUT         -586.00

(16 row(s) affected)

Using the Common Table Expression in MSSQL

WITH bstatement (num, emp, tdate, trans, amt, overdraft, bal) AS
(
SELECT a.counter ,
       a.emp_short ,
       a.trans_date ,
       CASE WHEN a.trans_type = 'IN' THEN 'Deposit' ELSE 'Withdrawl' END,
       CASE WHEN a.trans_type = 'IN' THEN a.amount ELSE -a.amount END,
       ' ',
      (SELECT SUM(b.amount)             
       FROM transactions AS b
       WHERE b.counter <= a.counter and
             b.emp_short = a.emp_short)
FROM transactions AS a
)
SELECT num AS 'Entry',
       emp AS 'Employee',
       tdate AS 'Date',
       trans AS 'Transaction',
       amt AS 'Amount',
       bal AS 'Balance',
       CASE WHEN bal < 0 THEN 'Overdraft' ELSE ' ' END AS 'Overdraft'
FROM bstatement
ORDER BY emp, tdate, num;
go

The format of the Common Table Expression is very simple:

  1. The Common Table Expression name just after the WITH keyword.
  2. The column list which is optional.
  3. The query within parentheses after the AS keyword.
  4. The calculations are done on the outside query. 
Entry       Employee   Date                    Transaction Amount                Balance               Overdraft
----------- ---------- ----------------------- ----------- --------------------- --------------------- ---------
1           000010     2007-05-01 00:00:00     Deposit     345.00                345.00                 
11          000010     2007-05-02 00:00:00     Deposit     175.00                520.00                 
21          000010     2007-05-03 00:00:00     Withdrawl   75.00                 445.00                 
31          000010     2007-05-05 00:00:00     Withdrawl   100.00                345.00                 
41          000030     2007-05-15 00:00:00     Deposit     10000.00              10000.00               
51          000030     2007-05-16 00:00:00     Deposit     435.00                10435.00               
61          000030     2007-05-20 00:00:00     Withdrawl   125.00                10310.00               
71          000030     2007-05-30 00:00:00     Withdrawl   1100.00               9210.00                
81          000110     2007-04-12 00:00:00     Withdrawl   250.00                -250.00               Overdraft
91          000110     2007-04-30 00:00:00     Withdrawl   75.00                 -325.00               Overdraft
101         000110     2007-05-10 00:00:00     Deposit     1000.00               675.00                 
111         000190     2007-06-01 00:00:00     Deposit     99.00                 99.00                  
121         000190     2007-06-01 00:00:00     Deposit     134.00                233.00                 
131         000310     2007-07-01 00:00:00     Deposit     1156.40               1156.40                
141         000310     2007-07-02 00:00:00     Deposit     67.00                 1223.40                
151         000310     2007-08-01 00:00:00     Withdrawl   586.00                637.40                 

(16 row(s) affected)
  • The query using the Common Table Expression must be the first query appearing after the definition of the Common Table Expression.
  • The Common Table Expression only exists for the duration of that one single query. It cannot be reused as a temporary table can be.