>No. A 'quantity remaining' column to track how many items from a particular P/A/T remain. And either using the existing cost column or perhaps (to be safe) a new one to record cost of items for each S. The alternative is having (as I think you are suggesting) to create a temporary table and repeat the same calculations from the year dot (with the same results) that have already been done every time the cost of a new sale is required.
>
>Even if you don't record the cost of sale at the time of entry your routine would only have to pick up transactions since the last time it was run.
>
>>I was thinking that if we add a new table called LastUsedInventory with the date time, cost tiers (quantity from-through and cost), quantity currently in,
>>then we should be able to calculate cost of goods sold at the time of the transaction (and we should save that in some other table).
>
>It's not clear to me how much this would help.....
>
>>This idea also assumes, that we always add rows with new dates into inventory and never try to adjust something in the past.
>>Right now this is not in place and we need careful planning and finding implementation, etc.
>
>>At the moment I'd like to be able to create the stored procedure (CLR if needed) to calculate cost of goods from the group up every time and do it as quick as possible.
>
>I quess the process is the same whether you start from the top every time or are able to pick up from the last run. Does this algorithm sound right (for an individual product):
>Get a list of all sales of the product, ordered oldest first.
>Get a list of all T/A/P transactions for the product, oldest first. Add a field for remaining items and set it equal to quantity purchased
>For each sale step through the T/A/P records reducing remaining items and moving to the next if ness. until the order is satisfied.
>Record total cost for that sale and move on to the next sale carrying on the process from the last used T/A/P record....

Did you check my thread on MSDN with the procedure I wrote? Do you have ideas of improvement? Don't also forget, that T can be with the negative quantities (transfer from that particular location out) and S can be with the positive quantities meaning returns. The returns cost of goods should also follow FIFO pattern.