CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Haminteu
Haminteu

Reputation: 1334

Calculate Every n record SQL

I have the following table:

oDateTime                oValue
------------------------------------
2017-09:30 23:00:00      8
2017-09-30 23:15:00      7
2017-09-30 23:30:00      7
2017-09-30 23:45:00      7
2017-10-01 00:00:00      6
2017-10-01 00:15:00      5
2017-10-01 00:30:00      8
2017-10-01 00:45:00      7
2017-10-01 01:00:00      6
2017-10-01 01:15:00      9
2017-10-01 01:30:00      5
2017-10-01 01:45:00      6
2017-10-01 02:00:00      7

The table will have one record every 15 minutes. I want to SUM or Average those records every 15 minutes.
So, the result should be:

oDateTime                Sum_Value      Avg_Value
---------------------------------------------------
2017-10-01 00:00:00      35             7
2017-10-01 01:00:00      32             6.4
2017-10-01 02:00:00      33             6.6

the SUM for 2017-10-01 00:00:00 is taken from 5 records before it and so on.
does anyone know how to achieve this?

Thank you.

Upvotes: 0

Views: 40

Answers (2)

Ab Bennett
Ab Bennett

Reputation: 1432

Just join the table to itself, and group by the master timestamp

This below is easily adjustable, to include how many minutes back you want. Handles change in frequency, i.e. doesn't assume 5 rows wanted, so if the data came in in 5 minutes intervals this is handled.

select cast('2017-09-30 23:00:00' as datetime) t,8  o
into #a
union all
select '2017-09-30 23:15:00',7 union all
select '2017-09-30 23:30:00',7 union all
select '2017-09-30 23:45:00',7 union all
select '2017-10-01 00:00:00',6 union all
select '2017-10-01 00:15:00',5 union all
select '2017-10-01 00:30:00',8 union all
select '2017-10-01 00:45:00',7 union all
select '2017-10-01 01:00:00',6 union all
select '2017-10-01 01:15:00',9 union all
select '2017-10-01 01:30:00',5 union all
select '2017-10-01 01:45:00',6 union all
select '2017-10-01 02:00:00',7 

select x.t,sum(x2.o),avg(cast(x2.o as float))
from   #a x, #a x2
where  x2.t between dateadd(mi,-60,x.t) and x.t 
group by x.t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is one method in SQL Server 2008:

select t.oDateTime, tt.sum_value, tt.avg_value
from (select oDateTime
      from t
      where datepart(minute, oDateTime) = 0
     ) t outer apply
     (select sum(oValue) as sum_value, avg(oValue) as avg_Value
      from (select top 5 t2.*
            from t t2
            where t2.oDateTime <= t.oDateTime
            order by t2.oDateTime desc
           ) tt
     ) tt;

In more recent versions of SQL Server, you can use window functions for this purpose.

Upvotes: 1

Related Questions