Post reply

Averaging cost

  • September 16, 2003 at 2:46 pm

    #358349

    I am trying to calculate average cost of the items we acquire. The table has four fields :

    Item, CostDate, Cost, AvgCost

    Avgcost, a calculated field, is the AvgCost for that item over a period of time and is calculated by averaging the previous cost

    records (based on costdate).

    Here is sample data:

    Item Costdate Cost AvgCost

    ----------------------------------------

    1 1/1/01 10 10

    1 1/2/01 20 15

    2 1/1/01 20 20

    2 1/2/01 30 25

    2 1/3/01 40 30

    My current data does not have avgcost and I am writing code to do that. My current plan is to use a cursor around the item.

    I am looking into better alternatives. Since averaging cost is a very common problem I am wondering if anyone has a better solution.

    thanks.

  • September 16, 2003 at 3:22 pm

    #474512

    sxg6023,

    Try this update statement:

    update ItemCosts

    set AvgCost = (SELECT SUM(Cost) FROM ItemCosts b WHERE b.Item = ItemCosts.Item AND b.CostDate <= ItemCosts.CostDate) /

    (SELECT COUNT(Item) FROM ItemCosts c WHERE c.Item = ItemCosts.Item AND c.CostDate <= ItemCosts.CostDate)

    This assumes that you table is named: ItemCosts

    Dave

  • September 16, 2003 at 3:36 pm

    #474516

    sxg,

    Is the average you wish to calculate a moving average (say, for purchases up to 90 days back)? Or... is it an average for all history of purchases? In either case, there are ways and ways to do this.

    If this is a one-off, perhaps the easiest way, IMHO (and MHO has been tested more often than not, here), would be something like:

     

    DECLARE		@tblWorkingData	table

    		(

    		ItemID		integer,

    		CostDate	datetime,

    		AvgCost		money

    		)

    

    INSERT INTO	@tblWorkingData

    		(

    		ItemID,

    		CostDate,

    		AvgCost

    		)

    SELECT		AC1.ItemID,

    		AC1.CostDate,

    		Avg(AC2.Cost) 'AvgCost'

    FROM		tblAvgCost AC1

    		JOIN

    		tblAvgCost AC2

    		ON AC1.ItemID = AC2.ItemID

    WHERE		AC1.CostDate >= AC2.CostDate

    GROUP BY	AC1.ItemID,

    		AC1.Cost,

    		AC1.CostDate

    

    UPDATE		tblAvgCost

    SET		AvgCost = WODA.AvgCost

    FROM		tblAvgCost AVCO

    		JOIN

    		@tblWorkingData WODA

    		ON AVCO.ItemID = WODA.ItemID

    		AND AVCO.CostDate = WODA.CostDate

    If you want something to calculate this cost ongoing, I'd recommend a stored procedure for your INSERTS that automatically calculates this value for you.

    HTH,

    --SJTerrill

  • September 16, 2003 at 4:34 pm

    #474523

    Thanks for your replies.

    The average is a moving average, in the sense that I need to take into consideration last 5 instances only.

    thanks in advance.

  • September 17, 2003 at 12:35 am

    #474562

    Hi sxg6023,

    quote:

    Thanks for your replies.

    The average is a moving average, in the sense that I need to take into consideration last 5 instances only.

    thanks in advance.

    not sure if I really follow you, but are you looking for

    <pseudo-sql>

    SELECT TOP 5 blabla FROM table ORDER BY costdate DESC / 5

    </pseudo-sql>

    well, if so , one might think that there isn't a need for a calculated field

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 17, 2003 at 10:01 am

    #474675

    The problem with this is that I may not have 5 past data values all the time. Some time I

    may have only 3. It would not let me say:

    select avg (top 5 cost)

    from table1

    order by costdate

    Any workarounds to use avg of top???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply