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

Compétences : Administration de Bases de données, Serveur SQL Microsoft, Oracle, Architecture Logicielle, SQL

en voir plus : change profile view numbers, change pic cartoon view, change myspace number friends profile view, fifo method, fifo template, fifo stock inventory sql problem, php templates change view, cost goods sold webform development, food cost goods sold calculators, magento change product view layout, change number profile view myspace, business card cost calc, javascript print cost calc, importing cost goods calculation, avg cost per inbound telemarketing call, avg cost making fillable pdf form, average cost per page view, caculator cost import goods, cost importing goods india, example inventory fifo cost

Concernant l'employeur :
( 0 commentaires ) Egypt

Nº du projet : #17465758

7 freelance font une offre moyenne de $298 pour ce travail


Hi i am expertise in sql please let me know

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 3 jours
(12 Commentaires)
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 10 jours
(9 Commentaires)
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 5 jours
(0 Commentaires)
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 10 jours
(0 Commentaires)
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 10 jours
(0 Commentaires)

we can help you regarding your CGS issue. it is very simple and you can do it in a very easy way. thanks.

%bids___i_sum_sub_32% %project_currencyDetails_sign_sub_33% USD en 1 jour
(0 Commentaires)
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 5 jours
(0 Commentaires)