>>>>>>>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?
>>>>>
>>>>>My SQL is lousy! I could have looked at that all day and been none the wiser :-}
>>>>>Is what you were doing basically the same as my suggestion ?
>>>>>
>>>>>>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.
>>>>>
>>>>>Ah. I didn't pick up on the fact that a S type transaction could be a sale *or* return. That would mean that when you hit a return transaction you would have to back up the T/A/P stack incrementing up to the original quan as required ?
>>>>
>>>>Can you clarify your exact idea of what tables should I add and how to populate them? I get a design footprint to work on for this.
>>>
>>>I wasn't advocating adding any tables at all. Just a 'Quantity remaining' field (initially set to the same value as the existing quantity field) and a 'Cost of Sale Column'. It might be possible to use the existing cost column for this but if there's a danger that adding values in that field might screw up current operations then best play it safe ? If both fields are added then they can't impact on the current table usage in any way.
>>>
>>>When it comes to calculating cost of sale for a new item then any T/A/P record with a remaing quan of 0 can be ignored as can any S record where the cost field has been populated so the routine will not be re-performing any calculations that have already been made.
>>>Whether this is done for every new S record or on an occasional basis the saving in processing time should be substantial ?
>>
>>So, quantity remaining for every transaction regardless on the type which is the running total of the qty and a new column for Cost Of transaction which is unit_cost * qty for P,A,T rows and calculated (how exactly qty remaining should help) for Sales rows?
>>
>>-------------------------------------------------------
>>E.g. suppose we have the following:
>>

>>Item                      Qty                         Trans_type  unit_cost
>>Widget                   100                         P                  $1
>>Widget                    100                        P                  $2
>>Widget                    -20                          S                   0
>>Widget                   -115                        S                    0
>>Widget                     25                         S                    0 -- this was a return
>>

>>--------------------------------------------------------------------------------------------------------------------------------------
>>Which fields should be here and how they are calculated (for each new transaction of any time) and how do they help?
>
>Let's assume that you have done the calculation upto and including the second sale. So the table would look like this:

>Item                      Qty                         Trans_type  unit_cost   Remaining  COS
>>Widget                   100                         P                  $1        0
>>Widget                    100                        P                  $2        65
>>Widget                    -20                         S                   0                         $20
>>Widget                   -115                        S                    0                        $150 
>>Widget                     25                         S                    0

Running it again (and we can ignore the first P and the first two sales) we get:

>Widget                   100                         P                  $1        0
>>Widget                    100                        P                  $2        90
>>Widget                    -20                         S                   0                         $20
>>Widget                   -115                        S                    0                        $150 
>>Widget                     25                         S                    0                        -$50

Disclaimer: I obviously just worked out the values in my head so they could be wrong :-}

How we add the negative transfer to the picture?