SQL Server Performance Forum – Threads Archive

Help with a query

Hello I have a table myTable(#Prod_Id, #Year, #Month, Price) which stores the price of a product given the year and month. Now, I would like to generate a resultset with the structure (#Prod_Id, #Year, #Month, AveragePrice), where AveragePrice is the average of the last six months. For example:
(1, 2007, 7, 10.0) –> 10.0 is the average price from January 2007 to June
(1, 2007, 8, 10.1) –> 10.1 is the average price from Febrary 2007 to July Is it possible to make a single query that produces the desired resultset? I would really appreciate any help that you can give me. Thanks a lot.

---

You can do a GROUP BY on the rest of the columns and use the AVG(price) to get the avg. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

]]>