Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Store a regularly updated calculation

Author  Topic 

mr_max
Starting Member

10 Posts

Posted - 2014-07-16 : 02:19:59
Hello, we have a business use case that requires a software dashboard to display the current production backlog in our small business. Using SQL Server 2012 Express.

ABC Co is our largest customer, they order a large number of items in a monthly order. We send these items in several shipments during the month, so the actual amount outstanding on each ABC Co order slowly decreases through the month. These orders are stored in the following tables:

ABC_OrdersSummary - stores each order - order no/date/total amount
ABC_OrderedProducts - stores each product - order no/product code/quantity
ABC_ShippedProducts - stores each shipped product - order no/product code/quantity

Products - stores all our products - product code/price etc.

To calculate the current outstanding orders value for ABC Co, we need to get the total of unfinished orders, then cycle through what has already been supplied on these orders, and subtract this value from the total unfinished orders value.

Is there a way to keep a running total in the ABC_OrdersSummary table of the amount outstanding on each order? It wouldn't matter if it only updated every 5min - it'd just be more efficient than 10-15 software instances all calculating it at once.

Sorry for the lengthy question, thanks in advance for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-16 : 04:01:26
You can add a trigger.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -