Suljettu

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/

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

Näytä lisää: 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

Tietoa työnantajasta:
( 0 arvostelua ) Egypt

Projektin tunnus: #17465530

17 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön

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] Lisää

$500 USD 10 päivässä
(37 arvostelua)
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 4 päivässä
(56 arvostelua)
5.2
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 10 päivässä
(9 arvostelua)
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 Lisää

$2000 USD 40 päivässä
(3 arvostelua)
4.5
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 2 päivässä
(19 arvostelua)
4.7
$561 USD 10 päivässä
(12 arvostelua)
3.9
$555 USD 10 päivässä
(9 arvostelua)
3.6
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 5 päivässä
(2 arvostelua)
3.2
$561 USD 10 päivässä
(3 arvostelua)
2.0
$166 USD 5 päivässä
(0 arvostelua)
0.0
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 Lisää

$166 USD 7 päivässä
(0 arvostelua)
0.0
$700 USD 4 päivässä
(0 arvostelua)
0.0
$500 USD 10 päivässä
(0 arvostelua)
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 Lisää

$111 USD 5 päivässä
(0 arvostelua)
0.0
$500 USD 5 päivässä
(0 arvostelua)
0.0
$561 USD 10 päivässä
(0 arvostelua)
0.0
$561 USD 10 päivässä
(0 arvostelua)
0.0