Use SQL Server to query the cumulative value

There is such a requirement that it creates an SQL Server query, including the cumulative value based on the event time. A typical example is a bank account, because every time you save and withdraw money at different times. For any account, you must calculate the sum of the loan (deposit) and loan (withdrawal) at a certain point in time. After each transaction, you want to know the current balance. List A creates A simple example of such A table.

List

Below are some examples:

1 02:33:42. 34010000.00

2 02:34:50. 467-500.00

3 02:35:04. 857250.00

4 02:42:19. 763-124.25

Because the date is set by default, you have to add some amount. For the sake of simplicity, assume that there is only one bank account (you can add a BankAccountNumber column to better suit the actual situation ).

Now you can create a query that includes the current balance. Because you have recorded deposit and withdrawal values with positive and negative values in the same column, the cumulative value in the column is 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.

List B

The following result set is obtained:

1 02:33:42. 34010000.00 10000.00

2 02:34:50. 467-500.00 9500.00

3 02:35:04. 857250.00 9750.00

4 02:42:19. 763-124.25 9625.75

As shown in this example, you can easily create a cumulative value as long as you understand the requirements. The example shown below assumes that the table contains only one account, but it is easy to process multiple accounts. Add a BankAccountNumber column and a WHERE predicate for the specified amount of interest.

You can also use this example, for example, to create a different operation in an inventory calculation. You start from Inventory 1,000 and then subtract all expenses and receipts.

This query has two advantages:

You do not need to store results. When you use an account number or a foreign key, the execution can be as fast as lightning.

You end the transaction with a tag, which can be checked row by row. If a problem occurs, you can isolate the transaction that causes the problem.