>>>>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.....

Are you suggesting to add a calculated column to the table?
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).

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.