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

Stängt Publicerat 5 år sedan Betalades vid leverans
Stängt

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

Project ID: 17465758

Databasadministration Microsoft SQL Server Oracle Programvaruarkitektur SQL

Projekt-id: #18435408

About the project

16 offerter Distansprojekt Aktivt 5 år sedan

16 frilansare har lagt bud på i genomsnitt £13/timme för det här jobbet

adeeladeel1234

I have extensive experience in database development and financial and inventory domain I can explain this cycle on detail and automate as per your requirements please send me message to discuss

£11 GBP / timme
(72 omdömen)
6.5
damianhill40

Hello, I have read your project description and the red gate article linked in it. I think it would be a good idea to build a stored procedure to handle the calculations, this would allow for a high performing ca Mer

£14 GBP / timme
(8 omdömen)
5.1
superbpartner

Hi,dear. I am very interested in your project - 'TSQL Calc Cost of goods Sold (FIFo,AVG ,LIFO) Sql server 2012'. I've already done this kind of project before. I'm a professional programmer with 12 years of experience. Mer

£13 GBP / timme
(8 omdömen)
5.1
alyelhaddad

Hi there - I'm Aly, I've seen your post and I'm interested. The raw data you posted is in text format which cannot be imported anywhere, I'd like to have a look over either generated query or the MDB file itself. I'm Mer

£15 GBP / timme
(16 omdömen)
4.8
Altantsetseg

Hello. From your project declaration, I strongly believe that I can complete this task successfully with your high satisfaction . I have been experienced in programming including MS SQL since 2000. During 2006-2013 Mer

£15 GBP / timme
(16 omdömen)
4.9
sreenivas2903

Hi. Iam expertise in sql server i would like to work o. This and dinish ur requirment if u willing to work ping me

£22 GBP / timme
(22 omdömen)
4.6
varshil8060

I am a PL/SQL expert with 9+ years of experience. I can help you write the Stored procedure in Sql server to meet your requirement. I just need to understand the schema of the database tables you have mentioned in the Mer

£13 GBP / timme
(7 omdömen)
4.4
vw7590795vw

Please message me. Please review my profile. https://www.freelancer.com/u/vw7590795vw.html I have 9.1 years experience in MS SQL Server and 6.4 years experience in SSRS, SSIS (Import / Export). Please let me know m Mer

£10 GBP / timme
(25 omdömen)
4.3
NoSonforhim

I have 17 years in Database Developing, Programming, Training, and Accounting Solutions / Finance with Computer

£10 GBP / timme
(5 omdömen)
2.6
avinpanwar300

Hello , Greetings !!! I just read your Project Description and i m willing to go ahead and start Development on this Project sending FIFO Stock Inventory Problem . I have done this kind of project recently i c Mer

£12 GBP / timme
(2 omdömen)
1.0
mhedhbi220456

DEVELOPER 2000 ORACLE REPORTS, DEVELOPER 2000 ORACLE FROMS PART II & I and DEVELOPER 2000 PROCEDURES. - ORACLE SQL AND PL/SQL. - DEVELOPER6i, DEVELOPER 9i, DEVELOPER 10G, DEVELOPER 11G

£13 GBP / timme
(0 omdömen)
0.0
tvboris4

Alright. That sounds like a cool problem to solve. I'm interested.

£11 GBP / timme
(0 omdömen)
0.0
pawand7

hi I have gone through the description of the project requirement and would be glad to assure that I can do well in this project if provided an opportunity. My skill set suits the project requirement and also my keen d Mer

£13 GBP / timme
(0 omdömen)
0.0
iabello

Working with: Installing and Configuring SQL Server Databases and Storage Planning and Implementing a Backup Strategy Importing and Exporting Data Whit Sql Server Integration Server SSIS Monitoring SQL Server with Mer

£13 GBP / timme
(0 omdömen)
0.0