60 Second Rolling Avg With Different Starting Points

  • Hi,

    I have a table with columns: jobId, timeStamp (for duration per jobId) and tempData (see attachment). I have created a query that calculates 60 second rolling average by jobId. (you can see it at the end of the post)

    Where I am struggling is adding two additional rolling averages. Both of them would be 60 second rolling avg, but with different starting points.

    One would start from 15 seconds above the current row and the other one would start from 15 seconds below the current row.

    So if the current row shows 2022-11-20 01:40:08.590, one measure would show avg 60 seconds back from 2022-11-20 01:39:53.590 and the other would show avg 60 seconds back from 2022-11-20 01:40:23.590.

    Also, for the first 60 seconds and for the last 15 seconds of each jobId - there would be no average (just NULL).

    Using PRECEDING/FOLLOWING ROWS for calculation will not work, as timestamps are not continuous (there are missing values). Avg has to be based on timestamp value.

    Below is the query I have so far for the rolling 60 second avg.

    with cte as (

    select jobId,


    dateadd(second, -60, timeStamp) AS lowerBound,


    FROM My_Table t


    select c.jobId,



    avg(m.tempdata) as MovingAvg

    from cte c

    inner join My_Table m

    ON m.jobId = c.jobId

    AND m.timeStamp between c.lowerBound and c.timeStamp

    group by c.jobId, c.timeStamp, c.tempData, c.lowerBound

    order by 1, 2

    Any help would be much appreciated!

    You must be logged in to view attached files.
  • This script can be used for sample data. Thank you.

    -- Setup some testdata

    if object_id('tempdb..#t_measures') is not null

    drop table #t_measures

    create table #t_measures (

    jobid int, offset int, tempdata float

    , dt datetime


    insert into #t_measures (

    jobid, offset, tempdata


    values (1, 0, 30)

    , (1, 1, 20)

    , (1, 3, 21.3)

    , (1, 10, 17.3)

    , (1, 55, 34.3)

    , (1, 62, 38.3)

    , (1, 68, 36.3)

    , (1, 75, 40.3)

    , (1, 80, 30)

    , (1, 90, 36)

    , (1, 105, 46)

    , (1, 155, 50)

    insert into #t_measures (

    jobid, offset, tempdata


    SELECT 2

    , offset + tempdata, tempdata - 5

    FROM #t_measures

    update t

    set dt = dateadd(second, offset, case when jobid = 1 then '20220101' else '20220201' end)

    from #t_measures t

    -- Calculate average between current measure and all measures at most 60 seconds back

    ;with cte as (

    select jobid, offset, dateadd(second, -60, dt) AS lowerBound

    , dt

    , tempdata

    FROM #t_measures t

    -- Optionally, add WHERE c.offset >= 60 or whatever you need to disregard


    select c.jobid, c.dt, c.lowerBound, c.tempdata, avg(m.tempdata) as MovingAvg

    from cte c

    inner join #t_measures m

    ON m.jobid = c.jobid

    AND m.dt between c.lowerBound and c.dt

    group by c.jobid, c.dt, c.tempdata, c.offset, c.lowerBound

    order by 1, 2

  • I'm having some difficulty because your sample data and results do not match what is produced by your query. For any situations where timestamps are repeated, like  20-Nov-2022 01:36:02, the moving average in your sample results requires some bit of ordering as a second criteria to not include both data values in the average. Add the situation where timestamps are repeated with the same tempData value and it looks like some sort of line number ordering would be needed.


  • Afaik this matches the output from the existing query.  To add additional columns you could add additional CROSS APPLY operators and then vary the date inequalities in the WHERE clause(s)

    select t.*, last_60_sec.mvg_avg
    from #t_measures t
    cross apply (select avg(tt.tempdata)
    from #t_measures tt
    where tt.jobid=t.jobid
    and tt.dt<=t.dt
    and tt.dt>dateadd(second, -60, t.dt)) last_60_sec(mvg_avg)
    order by t.jobid, t.dt;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply