How to use SQL Server database query cumulative values

This article describes how to use SQL Server database query cumulative values, for example analysis.

There is a requirement that it create a SQL Server query that includes cumulative values based on the event time. A typical example is a bank account, because you are saving and taking money at different times each time. For any account, at a point in time to calculate its borrowing (deposits) and credit (withdrawal) of the sum. After each transaction, you want to know the current balance.

List A creates a simple example of such a table.

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

List A

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

Because the date is set by default, all you have to do is add some amount. For simplicity, for example, suppose you have only one bank account (you can add a bankaccountnumber column for more realistic purposes).

Now you can create a query that includes the current balance. Because you have recorded the deposit and the withdrawal value in the same column with positive and negative values, the cumulative value in the column is very easy to calculate. To get the current balance, you need to calculate the total value of all previous transactions, and then add this value to the current transaction. The query in list B implements 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

List B

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 shows, the cumulative value can be easily established as long as you understand the requirements. The example below assumes that only one account is included in the table, but it is also easy to handle multiple accounts. Add a bankaccountnumber column and a WHERE predicate that specifies the amount of interest.

You can also use this example upside down, such as creating a different run in an inventory calculation. You start with inventory 1,000, then subtract all the expenses and receipts.

There are two advantages to such a query:

You don't need to store the results. Execution can be as swift as lightning when operating with account number or foreign key.

You end a transaction with a label that can be checked line by row. If there is a problem, you can isolate the transaction that is causing the problem.