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 |
|
shd66
Starting Member
2 Posts |
 Posted - 2013-07-31 : 06:31:47
|
| HiI have a database for each following departments Purchases (grn, grn return)Sales (deliveries, delivery/sale return)Stores (store issues, returns, production receive, lost, found)Now I have an separate database for stock movements, each time there is a transaction in either of the above departments, data will be replicated/copied/updated into stock movement tablesNow I have complete stock movement register in a single table ( stock movements )I wrote some procedures to calculate stock valuation for each item including running totals, moving average rate fro each transaction etc.Now the problem is every time a departmental database is updated/inserted/deleted I have to run valuation process again so that valuation need to be ready for critical reporting i.e. consumption reports, production costing etcMy stock_movement table have multi-milltion rows/transactions for each item, so online valuation is taking time and form-submission is slowany suggestionsRegards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-31 : 07:39:30
|
| why do you need to run entire valuation process for each insert/update.delete? dont you just need to capture the "deltas" since last run and do calculation only for the involved ones?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
shd66
Starting Member
2 Posts |
Posted - 2013-08-01 : 06:00:06
|
| a back dated entry will actually force to change entire valuation sheet when using moving average method... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 06:13:09
|
quote:
Originally posted by shd66
a back dated entry will actually force to change entire valuation sheet when using moving average method...
not the entire data but only from time period from which you did the change------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Inventory Stock Valuation Technique