Aggregate values at hourly interval using SQL server query

I have below test data in the table

ID  TEST_DATE          Value
1   10/21/2019 0:00     10
1   10/21/2019 0:15     10
1   10/21/2019 0:30     10
1   10/21/2019 0:45     10
1   10/21/2019 1:00     20
1   10/21/2019 1:15     10
1   10/21/2019 1:30     10
1   10/21/2019 1:45     10
1   10/21/2019 2:00     30

I have written below SQL query to aggregate the values at hourly interval

SELECT ID, dateadd(hour, datediff(hour, 0, TEST_DATE), 0) as ACTUAL_DATE,SUM(VALUE) as ACTUAL_VALUE 
FROM TEST_TABLE
GROUP BY dateadd(hour, datediff(hour, 0, TEST_DATE), 0) ,ID

currently getting results as

ID   ACTUAL_DATE       ACTUAL_VALUE
1    10/21/2019 0:00    40
1    10/21/2019 1:00    50

What should be my SQL query , If i want the hourly aggregation to consider from 15th minute instead of 0th minute .

Like first hour should have aggregation of

2019-10-21 00:15:00 - 10
2019-10-21 00:30:00 - 10
2019-10-21 00:45:00 - 10
2019-10-21 01:00:00 - 20

And the results should look like

ID   ACTUAL_DATE       ACTUAL_VALUE
1    10/21/2019 0:00    50
1    10/21/2019 1:00    60

Answer

The following expression will map the dates such as 2019-10-21 00:15, 2019-10-21 00:30, 2019-10-21 00:45 and 2019-10-21 01:00 to 2019-10-21 00:15:

SELECT DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, TEST_DATE) - 15) / 60 * 60 + 15, 0) AS ACTUAL_DATE

Use this expression in your GROUP BY.

Advertisement

Answer