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.
Listing A
creates a simple example of such a table.
Here are sample rows:
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.
There are two advantages to such a query:
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.
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.
No comments:
Post a Comment