>Hi Naomi.
>Have a routine in VFP for FIFO, but always adopted the methodology where, as soon as a transaction is posted, it calls the FIFO routine.
>Basically, I maintain a 'FIFO' table for each Positive Transaction, which has the Movement In, as well the balance remaining on that movement, so that as soon as a sale is made, it goes back, in date order, over evry non zero Balance remaining, and starts decremetning the Balance remaining.
>
>This is implemented live on an inventroy stock file with about 30,000 items , and takes less than a second
>
>I realise this is different than what you are trying to do, but might have some pointers.
>regards,
>Gerard
>
>
This is a good idea. I brought the idea of extra table up, so we may start implement it soon.

Although not all our clients use FIFO which we introduced relatively recently. Before we were using Running Weight Average although our implementation is not exactly correct as we always just get the whole inventory and get the average.


>
>
>>>>Hi everybody,
>>>>
>>>>I am thinking of attempting to write a CLR stored procedure. I am not sure how to approach this problem correctly in terms of C# coding (what should I use).
>>>>
>>>>Here is the problem:
>>>>
>>>>I have an inventory table. For simplicity let it be
>>>>
>>>>ItemID (in reality there are 5 fields that define inventory (department, category, item, invent_id, locationID))
>>>>
>>>>Trans_Type (P, A, T - purchase, adjustment, transfer) and S - sales
>>>>
>>>>Quantity - P,A,T can be negative (if transfer out), S - sales always negative, returns are positive
>>>>
>>>>Unit_Cost - only P,A,T rows have unit cost.
>>>>
>>>>-------------------------------------------
>>>>My task is to figure cost of goods sold for each sale (or return) starting from some date using FIFO method (first in/first out even for returns).
>>>>
>>>>So, I get all inventory first into a temp table and now I want to implement a CLR stored procedure to calculate the cost of goods sold.
>>>>
>>>>---------------------------------------------------------------------------
>>>>I have a stored procedure in T-SQL, that does it. However, since we need some sort of running totals there and I need this SP to work for SQL 2005 and 2008 (in SQL 2012 I can use ordered sum), it is very slow.
>>>>
>>>>Can you please show me (in pseudo-code) how the basis of this SP will look like?
>>>
>>>General question really : How, if using FIFO, can you start from a specific date. I don't see how you can determine what stock has already been sold without starting at the top ?
>>
>>Sorry, I meant that in my final output I only want to get few most recent sales cost of goods sold. But yes, of course, we have to start from the very first transaction and work all the way up. Just I will throw out most of the results as I only need to get cost of goods sold for the particular time frame. But to get the cost I have to work through the whole inventory movements.
>>
>>In theory, we can maintain a table with CostTiers and may be a field saying (UsedOnDate). If we have such a table, then we would be able to start later.
>>
>>Unfortunately, we don't have Cost Tiers table. I've been thinking of creating it and maintaining via trigger. That's a second possibility vs. CLR SP.
>>
>>BTW, this is my current T-SQL code using cursor based solution. I am not sure it's efficient
>>http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/5a8191b6-39e0-44ce-b13b-93a43c5478ff
>>, but I think the logic there is correct.
>>
>>I may need help translating it into C#