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

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