Calculating moving average cost rate in SQL Server

5 pts.
Tags:
SQL Server
I am trying to calculate a moving average rate for stock valuation and profit margin calculation, we have following table:
ProductID   PurchaseQty PurchaseRate    SaleQty   SaleRate  CostRate(Auto Calculate)
=========================================================================
1       4       500     0       0   500  =(2000/4)
1       6       600     0       0   560  =(3600+2000)/10
1       0       0       3       700 560  = On sold last Running Average rate is applied
1       5       700     0       0   618.33 =(3500+(7*560))/(7+5) 'Here 7 is Previous Stock + 5 is current stock 
1       0       0       4       800 618.33 = It is sold on last average price
Can anyone help to write a SQL Server Query calculate CostRate without using cursors and I need an efficient time saved query?
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Sounds like homework. Show us the code you have written and we can help clear up any issue you are having.
    131,645 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: