>>>>>>>>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#
>>>>>
>>>>>Could you move to a system that records the cost of sale at the time of sale?
>>>>>If you added a column that tracks quantity remaining as well as quantity purchased and decremented this at time of sale then the processing required would be minimal.
>>>>>
>>>>>If you can do that then you would only need to run the type of procedure you are talking about just once to get the starting position so efficiency shouldn't be a big issue.....
>>>>
>>>>I worked with inventory control software for the first 10 years of my career so have some familiarity with these issues.
>>>>
>>>>A table holding receipt information is definitely needed. Actually two of them, header and details. Likewise you need two tables with shipment information. The cost is part of both detail records. And you need a small piece of logic to reset FIFO cost to zero when inventory of the item falls to zero. This was a time of high inflation so a lot of companies were switching from FIFO to LIFO cost valuation.
>>>
>>>But if inflation is high wouldn't LIFO make inventory valuation look low ?
>>>
>>
>>They wanted cost to look high, minimizing taxable profit.
>
>Ah OK. I guess it's a question of which way you want to slant the figures :-}

Well, corporate taxes aren't just spin / slant. Those are real dollars coming off the bottom line.