|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.25Since 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 D0Here 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.75We 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.
Last updated 22 May 2009