September 16, 2003 at 2:46 pm
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
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
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
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
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
--
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
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