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

Evner: Database Administration, Microsoft SQL Server, Oracle, Software Arkitektur, SQL

Se mere: 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

Om arbejdsgiveren:
( 0 bedømmelser ) Egypt

Projekt ID: #17465758

7 freelancere byder i gennemsnit $298 på dette job


Hi i am expertise in sql please let me know

$250 USD in 3 dage
(12 bedømmelser)
$255 USD in 10 dage
(9 bedømmelser)
$277 USD in 5 dage
(0 bedømmelser)
$555 USD in 10 dage
(0 bedømmelser)
$250 USD in 10 dage
(0 bedømmelser)

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

$250 USD på 1 dag
(0 bedømmelser)
$250 USD in 5 dage
(0 bedømmelser)