SQL query with loop to collect 14 day averages

I am a python user, new to SQL. I am trying to make a new table that is the average of the “NumberValue” column between two dates from “RecordDate” column, and slide that date range so that the new table has columns “average” that is the average of “NumberValue” over the date range, “start_date” that is the beginning of the range, and “end_date” that is the end of the range. I can do this for a single date range at a time with something like:

SELECT AVG(NumberValue) AS average, DATEADD(DAY, -14, GETDATE()) AS start_date, DATEADD(DAY, 0, GETDATE()) AS end_date
FROM ResultsData
WHERE RecordDate BETWEEN DATEADD(DAY, -14, GETDATE()) AND DATEADD(DAY, 0, GETDATE())

but would like to slide the -14 and 0 with some sort of loop. Something like looping over the expression below with i and j changing by -14 each iteration and appending the new row each iteration:

SELECT AVG(NumberValue) AS average, DATEADD(DAY, i, GETDATE()) AS start_date, DATEADD(DAY, j, GETDATE()) AS end_date
FROM ResultsData
WHERE RecordDate BETWEEN DATEADD(DAY, i, GETDATE()) AND DATEADD(DAY, j, GETDATE())

I’d also like to make sure the date ranges are within what is available in the table, I’m assuming I can do this with some sort of a WHILE RecordDate > MIN(RecordDate) inside the loop?

Can anyone suggest the best way to do this?

Answer

This was what worked in the end:

SELECT A.start_date, AVG(A.NumberValue) FROM
(SELECT *, DATEADD(dd, -14, DATEFROMPARTS(YEAR(RecordDate), DAY(RecordDate))) as start_date FROM ResultData) A
GROUP BY A.start_date
ORDER BY A.start_date

credit goes to a coworker.

Advertisement

Answer