TSQL Calc Cost of goods Sold (FIFo,AVG ,LIFO)
$250-750 USD
Pagado a la entrega
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
Nº del proyecto: #17465758
Sobre el proyecto
7 freelancers están ofertando un promedio de $298 por este trabajo
we can help you regarding your CGS issue. it is very simple and you can do it in a very easy way. thanks.