February 18, 2002 at 7:50 am
I have a database with a view that displays current inventory at any given moment, I need to be able to show ending inventory for every day prior to now. Due to issues with old data, I cannot calculate ending inventory from the beginning of time, I must use the current ending inventory as an anchor and calculate backwords from there. The problem is, When I query my daily total for by item for sales, closeouts, adjustments, returns ect.. there has been activity on these records irregardless of the time of day. So if I get the current inventory, then get the activity from eachj of the activity types, data has moved enough to where my ending inventory calculations will not be consitent. Any ideas?
February 18, 2002 at 8:19 am
Probably the easiest way would be to select all your data into one or more parallel temp tables, then do the calculations from there.
Andy
February 18, 2002 at 9:48 am
I tend to agree with Andy from your description.
I've run some inventory things and for reconciliation and high accuracy, we used to use separate tables for calculations.
Steve Jones
February 18, 2002 at 10:32 am
quote:
I must use the current ending inventory as an anchor and calculate backwords from there
It sounds like what you are running into is needing a warehousing solution. Have you considered building a separate database and at the end of a given day, pushing data from the live database to it? In that manner, you're not having to worry about data changing underneath you.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 3:02 pm
Plus with Brians statement you could write it as a summary of the records so if someone wants trending on the values you do not have to recalc saving you server overhead with a bit of space overhead in the long run.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply