Need help trying to query 10 rows averaged data from 2 years ago when query runs.

Question

I am having an issue writing a query that works for averaging 10 rows of data from 2 years ago.

The below query is just to grab the data, the second one I tried averaging that data in a single query, but instead of grabbing the next 10 rows, it is grabbing all rows to beginning of timestamp.

---

SELECT TOP (10) [timestamp]

,[COLUMN_NAME]

FROM [DATABASE].[dbo].[TABLE]

where timestamp > DATEADD(YEAR, -2, GETDATE())

---

SELECT TOP 1 [timestamp], AVG([COLUMN_NAME])

OVER (ORDER BY (DATEADD(year, -2, GETDATE())) ROWS BETWEEN 10 PRECEDING CURRENT ROW) AS COLUMN_NAME_24MO

FROM [DATABASE].[dbo].[TABLE]

--
The second query is not working as intended. I think I'm missing something basic that I just can't see. I want to go back 2 years and grab only 10 rows from that time and average that data into the new name.

Answer 1

Hi,@hungryhippo13
For your first query, why is there no order by statement？
In your second query,

OVER (ORDER BY (DATEADD(year, -2, GETDATE())) ROWS BETWEEN 10 PRECEDING CURRENT ROW)   

This sentence cannot get 10 rows of data from 2 years ago.Please refer to this document for more details about over clause.
You need to make sure that you get 10 rows of data from 2 years ago,and then calculate avg.
Maybe you can have a try like this:

;WITH CTE AS   
(  
 SELECT TOP (10) [timestamp],[COLUMN_NAME]  
 FROM [DATABASE].[dbo].[TABLE]  
 WHERE timestamp > DATEADD(YEAR, -2, GETDATE())  
 ORDER BY [timestamp]  
)SELECT AVG(COLUMN_NAME)AS COLUMN_NAME_24MO  
 FROM CTE  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.