quote:

---

Originally posted by dgburton1

Hi,

I really appretiate your help so far! :)

I am running the following query along the lines of what you suggested, using a static ProductId:

SELECT p.Name, ph.DateTime, AVG(UnitPrice) AS AveragePrice
FROM Products p
INNER JOIN RawProducts rp
ON rp.ProductId=p.ProductId
INNER JOIN RawProductPriceHistory ph
ON ph.RawProductId = rp.RawProductId
where p.ProductId='962FE285-75CA-4E18-ADE1-10E066DBE26B'
GROUP BY p.Name, ph.DateTime
ORDER BY DateTime DESC

it gives these results:

Name DateTime AveragePrice
Click 2008-05-19 16:14:29.430 2.08
Click 2008-04-26 15:14:32.437 1.99
Click 2008-04-26 12:56:13.530 2.08
Click 2008-03-20 12:37:17.847 1.99
Click 2008-03-19 16:23:57.097 1.99
Click 2008-03-17 14:58:29.830 2.19
Click 2008-03-14 00:47:14.513 1.65

However as you can see, for 2008-04-26 there are two entries - I assume this is because there were 2 entries in the RawProductPriceHistory for that date, how can I get the query to return just 1 row for each date? - in addition I'd like to strip the DateTime of it's Time value - i.e. just return the date.

Once this query is working correctly I'd like to move on to do the same but passing in a CategoryId.

Thanks again! :)

---

SELECT p.Name, DATEADD(d,DATEDIFF(d,0,ph.DateTime),0) AS DateTime, AVG(UnitPrice) AS AveragePrice
FROM Products p
INNER JOIN RawProducts rp
ON rp.ProductId=p.ProductId
INNER JOIN RawProductPriceHistory ph
ON ph.RawProductId = rp.RawProductId
where p.ProductId='962FE285-75CA-4E18-ADE1-10E066DBE26B'
GROUP BY p.Name, DATEADD(d,DTAEDIFF(d,0,ph.DateTime),0)
ORDER BY DateTime DESC

quote:

---

Originally posted by dgburton1
DateTime AveragePrice
2008-05-19 (2.08 + 2.19 + 2.08 + 1.99) / 4 = 2.085 (Sainsburys price increased from 1.99 to 2.08)
2008-04-26 (1.99 + 2.19 + 2.08 + 1.99) / 4 = 2.06 (Sainsburys price increased from 1.65 to 1.99)
2008-03-20 (1.65 + 2.19 + 2.08 + 1.99) / 4 = 1.977 (Tesco price increased from 1.99 to 2.08, product now stocked at Boots)
2008-03-19 (1.65 + 2.19 + 1.99) / 3 = 1.94 (product now stocked at Tesco)
2008-03-17 (1.65 + 2.19) / 2 = 1.92 (product now stocked at Superdrug)
2008-03-14 1.65 (product stocked at Sainsburys)

---

quote:

---

Originally posted by dgburton1

Thanks for your quick response! - really appretiate all you help!

However I get the following when the query is run:

Msg 8120, Level 16, State 1, Line 2
Column 'Price_CTE.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

many thanks :)

---

;With Price_CTE (Seq,Date,Price,Name,ProductId) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY rp.ProductId ORDER BY rpph.DateTime),
DATEADD(d,DATEDIFF(d,0,rpph.DateTime),0), rpph.UnitPrice, s.Name, rp.ProductId
FROM
RawProductPriceHistory rpph
JOIN RawProducts rp
ON rpph.RawProductId = rp.RawProductId 
JOIN Shops s
ON s.ShopId = rp.ShopId
)

SELECT c1.Date,c2.AvgPrice
FROM Price_CTE c1
OUTER APPLY (SELECT SUM(Price)*1.0/COUNT(*) AS AvgPrice
             FROM Price_CTE t
             INNER JOIN (SELECT Name,MAX(Date) as maxdate
                         FROM Price_CTE
                         WHERE ProductId=c1.ProductId
                         AND Seq<=c1.Seq
                         GROUP BY Name)t1
              ON t1.Name=t.Name
              AND t1.maxdate=t.Date
              WHERE t.ProductId=c1.ProductId
              AND t.Seq<=c1.Seq)c2
WHERE c1.ProductId = '962fe285-75ca-4e18-ade1-10e066dbe26b'
ORDER BY c1.Date DESC

quote:

---

Originally posted by dgburton1

Thanks - that looks like it does what we need! :)

Two questions,

Could we extend the query so it takes in as parameters:
@Date1 <- the start date
@Date2 <- the end date
@ProductIds <- comma seperated list of ProductId's to get data from - at the moment it only takes data from 1 ProductId, but in reality we'll need it to retrieve from multiple ProductIds.

The query should retrieve data between @Date1 and @Date2.

Also - the query seems to run quite slowly - just running the query above takes nearly 40 seconds, is there any way to optimize/speed up the execution?

Thanks again for all your help - really appretiate it!

---