Running totals in SQL Server queries

by Arthur Fuller | Nov 06, 2006 3:35:00 PM

Tags: Databases, Arthur Fuller, current balance, Microsoft SQL Server, withdrawal, SQL Server Newsletter

Takeaway: Arthur Fuller provides an example of how to run totals in SQL Server queries. Two advantages to such a query include: You don't have to store the results, and you end up with a transaction log that can be inspected row by row.

The requirement is to create a SQL Server query containing running totals based on the moment of the event. The classic example is a bank account because you make deposits and withdrawals, each at a different moment in time. For any given account, one sums the debits (deposits) and credits (withdrawals) at a given point in time. After each transaction, you want to know the current balance. Listing A creates a simple example of such a table.

Here are some sample rows:

1���� 2006-11-03 02:33:42.34010000.00

2���� 2006-11-03 02:34:50.467-500.00

3���� 2006-11-03 02:35:04.857250.00

4���� 2006-11-03 02:42:19.763-124.25

Since the date is defaulted, all you need to do is add a few amounts. The example keeps it simple, assuming only one bank account (add a column BankAccountNumber for a more realistic scenario).

You are now in a position to create the query that contains the current balance. Since you are recording deposits and withdrawals in the same column as negatives and positives, the sum of the columns is straightforward. To derive the current balance, you sum all the previous transactions and add this sum to the value of the current transaction. The query in Listing B accomplishes this.

This results in the following result set:

1���� 2006-11-03 02:33:42.34010000.00��� 10000.00

2���� 2006-11-03 02:34:50.467-500.00���� 9500.00

3���� 2006-11-03 02:35:04.857250.00����� 9750.00

4���� 2006-11-03 02:42:19.763-124.25���� 9625.75

As this example demonstrates, running totals are simple to create, once you understand the requirements. The example presented assumes that the table contains only one account, but it's easy to deal with many 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 thus 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:

You don't have to store the results. When scoped by an account number or similar foreign key, performance can be lightning fast.
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.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recently published SQL tips.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

14 comment(s)