TSQL Calc Cost of goods Sold with the three method FIFO ,LIFO ,Avg

I Use Sql server 2012

I have ERP System and I need to calc FIFO,LIFO and Average cost in for the inventory Item

I have 4 types of transaction 'IN' (Purchase) ,'OUT' Sale ,'INR' Returned Purchase , ''OUTR' for Sales return

Database Structure

- Items Table ( Has ItemID, And ItemID 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 depened 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


3 1000 6 0 Out 14 240


3 1000 6 0 Out 14 180

If In 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

Skills: Microsoft SQL Server, SQL, T-SQL (Transact Structures Query Language)

See more: cost goods sold webform development, food cost goods sold calculators, importing cost goods calculation, fifo stock inventory sql problem, fifo method in sql server, how to implement fifo in sql server, sql, database development, calculate fifo inventory, zencart shipping cost based payment method, commissions paid statement cost goods, fifo php source code, fifo inventory source code, vtiger cost goods sold, magento cost goods sold, fifo inventory mysql code, fifo page replacement code, runge kutta method order visual basic code, 25a compute cost goods manufactured cost goods sold, calculate fifo gains excel

About the Employer:
( 0 reviews ) Egypt

Project ID: #17465530

15 freelancers are bidding on average $538 for this job


I have more than 7 years of professional and practical experience in SQL Server, TSQL, and database development. I have more than 1800 answers in Stackoverflow(Q&A Site) [login to view URL] More

$500 USD in 10 days
(38 Reviews)

Hi, I would like to write t sql code for getting the average value in case of fifo, lifo, avg Get in touch to discuss more. Available to start now. Regards, Manish

$200 USD in 4 days
(62 Reviews)

Sir I have vast experience in databases and TSQL,u can view my profile and complete on time.I am a full time freelancer, working for more than 2 years now. Have clients from different parts of the world.

$783 USD in 2 days
(25 Reviews)

Hi, I got invitation from you to bid on this project , please do let me know when you available so we could discuss in detail. thanks

$561 USD in 10 days
(9 Reviews)

I have extensive experience as a Commercial Analyst including work on costings, which lead on to work as a Database Developer and DBA with most of my current work in database development. I would be pleased to do this More

$2000 USD in 40 days
(3 Reviews)
$561 USD in 10 days
(13 Reviews)

Hello, Can you tell me is your database relational & you design your scheme using constraints? It will be better if you give me the relation of both tables. Thanks zakir

$150 USD in 5 days
(2 Reviews)
$555 USD in 10 days
(9 Reviews)
$561 USD in 10 days
(4 Reviews)

Hello Sir, I have read your problem. You need calculate your Avg, Fifo and Lifo after any entry update or insert. I can do it for you. I have work before this type of task in my office. If any Item Purchase then stor More

$166 USD in 7 days
(0 Reviews)

Hi, I am Avijit Jaiswal with total 4 years of experience in IT industry, and I have knowledge of BI,SQL, Data Warehousing, Big Data(HIVE), ETL & Cloud Computing(AWS Components like - Redshift, EC2, SWF, RDS), ETL to More

$111 USD in 5 days
(0 Reviews)
$700 USD in 4 days
(0 Reviews)
$166 USD in 5 days
(0 Reviews)
$500 USD in 10 days
(0 Reviews)
$561 USD in 10 days
(0 Reviews)