Ditutup

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

If In FIFO

3 1000 6 0 Out 14 240

If In LIFO

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

https://www.red-gate.com/simple-talk/sql/performance/t-sql-window-function-speed-phreakery-the-fifo-stock-inventory-problem/

Kemahiran: Pelayan Microsoft SQL, SQL, T-SQL (Transact Structures Query Language)

Lihat lagi: 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

Tentang Majikan:
( 0 ulasan ) Egypt

ID Projek: #17465530

16 pekerja bebas membida secara purata $536 untuk pekerjaan ini

mahmoudgamal008

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] Lagi

$500 USD dalam 10 hari
(37 Ulasan)
5.6
softcarve

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 dalam 4 hari
(59 Ulasan)
5.3
hamzzaamalik

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 dalam 2 hari
(21 Ulasan)
4.8
IMExpertEngineer

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 dalam 10 hari
(9 Ulasan)
4.8
adrianbrough

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 Lagi

$2000 USD dalam 40 hari
(3 Ulasan)
4.5
$561 USD dalam 10 hari
(12 Ulasan)
3.9
csezakir

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 dalam 5 hari
(2 Ulasan)
3.2
$555 USD dalam 10 hari
(9 Ulasan)
3.6
$561 USD dalam 10 hari
(4 Ulasan)
2.2
aminuridbr25

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 Lagi

$166 USD dalam 7 hari
(0 Ulasan)
0.0
avijaiswal08

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 Lagi

$111 USD dalam 5 hari
(0 Ulasan)
0.0
$700 USD dalam 4 hari
(0 Ulasan)
0.0
$166 USD dalam 5 hari
(0 Ulasan)
0.0
$500 USD dalam 10 hari
(0 Ulasan)
0.0
$500 USD dalam 5 hari
(0 Ulasan)
0.0
$561 USD dalam 10 hari
(0 Ulasan)
0.0