TSQL Calc Cost of goods Sold (FIFo,AVG ,LIFO)

I Use Sql server 2012 I have ERP System and I need to calc FIFO,LIFO and Average cost for the inventory Items I have 4 types of transaction 'IN' (Purchase) ,'OUT' Sale ,'INR' Returned Purchase , ''OUTR' for Sales return Database Structure - Items Table ( Has ItemID, And Item Name and Calculation Method ) -Transaction Table (has ItemID,Date,Qty,Price,TransactionCode) I need To calculate the Avg ,Fifo And Lifo after any Update or save in the Transaction table because all data depend on the Transaction Date , for example After Purchase ID ItemID Qty Price TransCode CurrentQty CurrentValue 1 1000 10 10 IN 10 100 2 1000 10 20 IN 20 300 Now Sales with FIFO 3 1000 6 0 Out 14 240 Sales With LIFO 3 1000 6 0 Out 14 180 Sales With Avg 3 1000 6 0 Out 14 210 ----- the Problem Will happen If the Customer Need to Delete the Transaction With ID=1 then I must re Calc All Rows again depend to the change , and this need very fast TSQL Code Please read the article as reference --- [login to view URL] ---- Please download the test data from the [login to view URL] Unzip the file and import the data (consisting of over a million rows) into the Stock table using your method of choice (I used the SQL Server Import Wizard). Run this query SELECT * FROM Stock to view the data