SQL Server aggregation by 15min

Question

SELECT 
    AVG(value) AS total, 
    DATEADD(minute, DATEDIFF(minute, 0, reading_time), 0) AS created
FROM 
     ...  
GROUP BY
     DATEADD(minute, DATEDIFF(minute, 0, reading_time), 0)

This query aggregates data by minutes.

How can I aggregate the same data in 15min intervals?

Possible duplicate of [SQL SERVER - Group records by n minutes interval](http://stackoverflow.com/questions/26788729/sql-server-group-records-by-n-minutes-interval) — Tab Alleman, Jun 27 '16 at 14:47

score 0 · Answer 1 · answered Jun 27 '16 at 14:50

Do the date/time manipulation in a derived table. Then GROUP BY that result:

select avg(value) as total, reading_date, reading_15_mins
from
(
    SELECT value,
           CAST(reading_time as DATE) as reading_date,
           DATEPART(MINUTE, reading_time) / 15 as reading_15_mins
    FROM tablename 
) dt
GROUP BY reading_date, reading_15_mins

Inspired by sagi's answer. Thanks!

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

I'd definitely create a date table for this. The advantage of this would be that you will return every time period even if there is no data.

Date Table for Testing

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL DROP TABLE #DateTable
GO
CREATE TABLE #DateTable (QuarterHour datetime)
INSERT INTO #DateTable (QuarterHour)
VALUES
 ('2016-06-27 12:00:00.000')
,('2016-06-27 12:15:00.000')
,('2016-06-27 12:30:00.000')
,('2016-06-27 12:45:00.000')
,('2016-06-27 13:00:00.000')
,('2016-06-27 13:15:00.000')

Sample Data

IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
GO
CREATE TABLE #SampleData (SampleID int identity(1,1), Reading_Time datetime, Value int)
INSERT INTO #SampleData (Reading_Time, Value)
VALUES
 ('2016-06-27 12:01:00.000',7)
,('2016-06-27 12:25:00.000',12)
,('2016-06-27 12:59:00.000',5)
,('2016-06-27 12:33:00.000',6)
,('2016-06-27 12:27:00.000',2)
,('2016-06-27 12:08:00.000',9)
,('2016-06-27 13:16:00.000',7)
,('2016-06-27 12:47:00.000',50)

Query

SELECT
dt.QuarterHour
,AVG(sd.Value) Value
FROM #DateTable dt
LEFT JOIN #SampleData sd
ON dt.QuarterHour = dateadd(minute, datediff(minute,0,sd.Reading_Time) / 15 * 15, 0)
GROUP BY dt.QuarterHour

Result Set

QuarterHour                 Value
2016-06-27 12:00:00.000     8
2016-06-27 12:15:00.000     7
2016-06-27 12:30:00.000     6
2016-06-27 12:45:00.000     27
2016-06-27 13:00:00.000     NULL
2016-06-27 13:15:00.000     7

There's some distinct advantages of a date table, performance time being one of them. Some more reading below if you're interested

https://dba.stackexchange.com/questions/74957/best-approach-for-populating-date-dimension-table

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

score 0 · Answer 3 · answered Jun 27 '16 at 15:17

I use a UDF to create dynamic date ranges (see below)

Declare @Date1 DateTime = '2016-06-27'
Declare @Date2 DateTime = '2016-06-28'
Declare @Incr  int      = 15

Select DateR1
      ,DateR2
      ,Total = avg(value)
 From YourTable A
 Join (Select DateR1=RetVal,DateR2=DateAdd(MI,@Incr,RetVal) from [dbo].[udf-Create-Range-Date](@Date1,@Date2,'MI',@Incr) Where RetVal<@Date2) B
   on Reading_Time between DateR1 and DateR2 and ReadingTime < DateR2

The UDF... there are many other techniques available.

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )
    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)

SQL Server aggregation by 15min

3 Answers3