SQL query with loop to collect 14 day averages

Question

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?

@Albert The query will ultimately run in thingworx using the MSSqlServer template — tcasey, Nov 18 '21 at 20:14
@GoldenLion I haven't done this in python yet as it will have to run in thingworx, I'm stuck with SQL and/or a loop in JavaScript calling the static version of the SQL query — tcasey, Nov 18 '21 at 20:18

Golden Lion · Answer 1 · 2021-11-18T23:16:33.103

1

you can use the over window to create a look back. The over uses the current row then averages 14 preceding rows of data for price. you can use matplotlib.pyplot to see the moving average against the actual data.

the range window will average by date accumulatively. The moving average does not change until the date changes.

select *,
  IsNull(avg(Price) OVER(ORDER BY Date
     ROWS BETWEEN 14 PRECEDING AND CURRENT ROW ),0)
     as moving_average
from stock_price;


try range



SELECT 
      cast([date] as date) as date
      ,[price]
      ,IsNull(avg([price]) over (order by cast([date]  as date)
      RANGE BETWEEN
          unbounded preceding and current row
      ),0) moving_average
  FROM stock_price

edited Nov 18 '21 at 23:16

answered Nov 18 '21 at 20:30

Golden Lion

3,840
2
26
35

`select *, avg(NumberValue) OVER(ORDER BY RecordDate ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) as moving_average from ResultData;` This gives the error "The index 1 is out of range" – tcasey Nov 18 '21 at 20:42
same exception: "Execute Query Failed: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range" – tcasey Nov 18 '21 at 20:57
run the query in ssm and see if you get any data. index 1 out of range means no data – Golden Lion Nov 18 '21 at 21:00
see (https://stackoverflow.com/questions/38655215/java-preparedstatement-com-microsoft-sqlserver-jdbc-sqlserverexception-index-o) remove the *, and run again. look for apostrophs in your data – Golden Lion Nov 18 '21 at 21:01
see (https://www.codeproject.com/Questions/5256457/Com-microsoft-sqlserver-jdbc-sqlserverexception-th) problems with parameterization – Golden Lion Nov 18 '21 at 21:03
there is definitely data, my simple query above works just returns the single row – tcasey Nov 18 '21 at 21:06
the bigger problem with this is that there is more than one row per date, the condition needs to be a date range not just 14 rows – tcasey Nov 18 '21 at 21:14
@tcasey: Change `ROWS` to `RANGE` to look at 14 dates rather than 14 rows. – Thorsten Kettner Nov 18 '21 at 21:18
14 preceding plus current row is 15 months. – shawnt00 Nov 18 '21 at 21:33
Still same exception. I don't think there is any issue with the data type or the data being there, my simple query I started with doesn't error no matter what range I put in manually. Maybe i'm not understanding but with `RANGE` on a datetime column to select 14 day periods I'd need to define the start and end dates of the `RANGE`, not just some number of preceding rows. Then i'm back to square one defining the starts and ends. I need an iterator where the start and end of the `BETWEEN` are shifting by 14 days per iteration. – tcasey Nov 18 '21 at 21:56
`RANGE` does exactly that. It considers all rows with the same sortkey - the date in your case - one block. You look back 14 blocks, which means 14 dates. – Thorsten Kettner Nov 18 '21 at 22:38
Have you tried running directly from a query tool? – shawnt00 Nov 18 '21 at 23:15
in the end it was much simpler than I was making it, a coworker provided the answer I posted. thanks everyone. – tcasey Nov 24 '21 at 22:15

score 0 · Answer 2 · answered Nov 18 '21 at 23:17

I think you want something more like this. If you aggregate all the daily averages you'll get down to one row per date. From that point it's easy to use the rolling window. Note that it does assume there will be at least one row to represent every date.

with params as (
    select cast(<start> as date) as startDt, cast(<end> as date) as endDt
), d as (
    select
        cast("date" as date) as dt,
        case when cast("date" as date) >= startDt then 1 end as keep,
        avg(NumberValue) as average,
        count(*) as weight 
    from T cross apply (select * from params) p
    where cast("date" as date) between dateadd(day, -13, startDt) and endDt
    group by cast("date" as date)
)
select dt,
    avg(average * weight)
        over (order by dt rows between 13 preceding and current row)
from d
where keep = 1;

score 0 · Accepted Answer · answered Nov 24 '21 at 22:11

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.

SQL query with loop to collect 14 day averages

3 Answers3