October 19, 2003 at 11:30 am
I have a table with the structure:
(The avg price blank to start with)
Period Item Price Vender AvgPrice
1X10A
2X20A
3X30A
4X20A
5X20A
6X40A
7X50B
8X20A
9X30A
10X20A
11X20A
12X40A
13X50B
The business rules require that we average price so that,
for each item for each period we consider last four periods for avg calculations for which the vendor
has been the same. So for item x, period 12 the average would be average of 9, 10, 11, and 12. Whereas
for period 10 of the same item, we would take only 8, 9 and 10 (since for period 7 the vendor is different).
The table structure above is the gist of the real one, which would have thousands of items.
Is there any way to calculate the average in a TSQL without going for the cursors?
Here is the psuedo TSQL for average price, but this does not take the constrain of
"until the vendor is not changed". I cannot say p.vendor = p1.vendor, because that would take
into account vendors before the current vendor has changed. That violates the business rule. I need to avg only until the vendor has remained the same (with in the last 4 periods of course).
Update price p
set avgprice = (select avg (price)
from price p1
where p.item = p1.item and
p1.period - p.period < 4)
Any leads would be appreciated.
Thanks in advance.
October 19, 2003 at 1:35 pm
Perhaps something like this:
UPDATE Price SET AvgPrice =
(SELECT AVG(x.Price)
FROM
(SELECT TOP 4 Price
FROM Price g
WHERE Period > ISNULL(
(SELECT MAX(Period)
FROM Price i
WHERE i.Item = Price.Item
AND i.Vendor <> Price.Vendor
AND i.Period < Price.Period),g.Period - 1)
AND g.Period <= Price.Period
AND g.Item = Price.Item
AND g.Vendor = Price.Vendor
ORDER BY g.Period DESC)x)
--Jonathan
--Jonathan
October 22, 2003 at 7:52 am
Brilliant! Thanks! That would work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply