Running totals

from the Artful SQL Server & Access Tips List

You are asked to write a query that returns time-based running totals. The classic example is a bank account.

For any given account, you sum the debits (deposits) and credit (withdrawals) up to that time. After each transaction, you want to know the current balance. Here are sample rows:

1     2006-11-03 02:33:42.340     10000.00
2     2006-11-03 02:34:50.467      -500.00
3     2006-11-03 02:35:04.857       250.00
4     2006-11-03 02:42:19.763      -124.25

Since you are recording deposits and withdrawals in the same column, as negatives and positives, the sum is straightforward. To derive the current balance, you sum all the previous transactions and add this sum to the value of the current transaction:

SELECT 
  transactionid,transactiondatetime,amount,
  (SELECT SUM(amount)
   FROM dbo.bankaccount as D1
   WHERE D1.transactiondatetime <= D0.transactiondatetime
  ) AS balance
FROM dbo.bankaccount AS D0

Here is the result:

1     2006-11-03 02:33:42.340     10000.00    10000.00
2     2006-11-03 02:34:50.467      -500.00     9500.00
3     2006-11-03 02:35:04.857       250.00     9750.00
4     2006-11-03 02:42:19.763      -124.25     9625.75

We assumed one account. To handle multiple accounts, add a column for BankAccountNumber and a WHERE predicate that specifies the account of interest.

You can also turn this example upside down and create a running difference (as in an inventory count). You begin with an inventory of 1,000, and then subtract various purchases and receipts.

There are two advantages to such a query:

1. You don't have to store the results. When scoped by an account number or similar foreign key, performance can be lightning fast.

2. You end up with a transaction log that can be inspected row-by-row. If a bug turns up, you will be able to isolate the particular transaction that caused it.
Return to the Artful SQL Server & Access tips page