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.
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 amountABC_OrderedProducts - stores each product - order no/product code/quantityABC_ShippedProducts - stores each shipped product - order no/product code/quantityProducts - 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 |
|
|
|
|
|
|
|