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


Habilidades: Servidor de Microsoft SQL , SQL, T-SQL (Transact Structures Query Language)

Ver más: 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

Información del empleador:
( 0 comentarios ) Egypt

Nº del proyecto: #17465530

15 freelancers están ofertando el promedio de $538 para este trabajo


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] Más

$500 USD en 10 días
(38 comentarios)

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 en 4 días
(62 comentarios)

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 en 2 días
(25 comentarios)

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 en 10 días
(9 comentarios)

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 Más

$2000 USD en 40 días
(3 comentarios)
$561 USD en 10 días
(13 comentarios)

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 en 5 días
(2 comentarios)
$555 USD en 10 días
(9 comentarios)
$561 USD en 10 días
(4 comentarios)

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 Más

$166 USD en 7 días
(0 comentarios)

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 Más

$111 USD en 5 días
(0 comentarios)
$700 USD en 4 días
(0 comentarios)
$166 USD en 5 días
(0 comentarios)
$500 USD en 10 días
(0 comentarios)
$561 USD en 10 días
(0 comentarios)