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
 Database Design and Application Architecture
 Inventory Stock Valuation Technique

Author  Topic 

shd66
Starting Member

2 Posts

Posted - 2013-07-31 : 06:31:47
Hi

I 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 tables

Now 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 etc

My stock_movement table have multi-milltion rows/transactions for each item, so online valuation is taking time and form-submission is slow


any suggestions

Regards

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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...
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -