none
Weighted Average Calculations in TSQL

    Question

  • Hi,
    Can someone please help me with the following.
    I wish to calculate a weighted average cost on a countback FIFO basis (First IN First Out) of my stock using T-SQL. I have a list of 1600 distinct parts who all have varying amounts of stock. For Example:

    Current Stock 15,000 as at 16/08/09

    Part             DeliveredQty           DeliveryDate             Unitprice
    18-980012       3000                       15/01/09               £0.18
    18-980012       4000                       15/02/09               £0.42
    18-980012       2800                       15/04/09               £0.29
    18-980012       1700                       15/06/09               £0.86
    18-980012       1750                       15/07/09               £1.12
    18-980012       1750                       15/08/09               £1.24

    Many Thanks in Advance

    Mofi
    Wednesday, September 16, 2009 11:08 AM

Answers


  • select PART, '£'+convert(varchar(20),sum(deliveredqty*unitprice)/SUM(deliveredqty)) as AveragePerItem from #m
    group by part

    PART           AveragePerItem
    18-980012   £0.57

    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

    • Proposed as answer by Bikash Dash Friday, September 18, 2009 7:50 AM
    • Marked as answer by Zongqing Li Tuesday, September 22, 2009 6:13 AM
    Friday, September 18, 2009 6:57 AM

All replies


  • select PART, '£'+convert(varchar(20),sum(deliveredqty*unitprice)/SUM(deliveredqty)) as AveragePerItem from #m
    group by part

    PART           AveragePerItem
    18-980012   £0.57

    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

    • Proposed as answer by Bikash Dash Friday, September 18, 2009 7:50 AM
    • Marked as answer by Zongqing Li Tuesday, September 22, 2009 6:13 AM
    Friday, September 18, 2009 6:57 AM
  • Hi Melissa,

    Thank you for your help. Though your suggestion does indeed give me a weighted average,I was hoping to work out the weighted average on a FIFO basis, i.e. First In First Out.

    Thanks
    Tuesday, September 22, 2009 3:05 PM
  • It is.
    FIFO is only applicable when you take something out from stock. Then you remove oldest item first and thus change current stock value.
    Melissa is right for the stock value calculation.
    Tuesday, September 22, 2009 6:12 PM