Running totals in SQL Server queries

You're tasked with creating a SQL Server query containing running totals based on the moment of the event. The classic example is a bank account.

For any given account, you sum the debits (deposits) and credit (withdrawals) at a given point in time. After each transaction, you want to know the current balance. The code below creates a simple example of such a table.

CREATE TABLE [dbo].[BankAccount](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionDateTime] [datetime] NOT NULL CONSTRAINT [DF_BankAccount_TransactionDateTime] DEFAULT(getdate()),
[Amount] [money] NOT NULL CONSTRAINT [DF_BankAccount_Amount] DEFAULT((0)),
[TransactionType] [char](1)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AccountNumber] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_BankAccount] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF) ON [PRIMARY]
) ON [PRIMARY]

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 the date is defaulted, all you need to do is add a few amounts. The example keeps it simple, assuming only one bank account. Now you can 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 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 following query accomplishes this:

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

This results in the following result set:

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

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—you would just need to 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:

|> 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.