Using SQL Server to query for cumulative values

Source: Internet
Author: User
Tags sql server query

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.

List A

Here are some examples:

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.

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.