TSQL Calc Cost of goods Sold (FIFo,AVG ,LIFO)

Lukket Opslået 5 år siden Betalt ved levering
Lukket Betalt ved levering

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

Database Administration Microsoft SQL Server Oracle Software Arkitektur SQL

Projekt ID: #17465758

Om projektet

7 bud Remote projekt Aktiv 5 år siden

7 freelancere byder i gennemsnit $298 timen for dette job

sreenivas2903

Hi i am expertise in sql please let me know

$250 USD in 3 dage
(12 bedømmelser)
3.9
Mastams

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)
0.0