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?

Thanks in advance.