CODEWITHSUNDEEP
sqlsql-servert-sql
mahesh
mahesh

Reputation: 3217

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

Upvotes: 2

Views: 712

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272156

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Subtract 15 minutes:

SELECT t.ID, v.ACTUAL_DATE, SUM(t.VALUE) as ACTUAL_VALUE 
FROM TEST_TABLE T CROSS APPLY
     (VALUES (DATEADD(HOUR,
                      DATEDIFF(HOUR, 0, DATEADD(MINUTE, -15, t.TEST_DATE)),
                      0
                     )
             )
     ) v(ACTUAL_DATE)
GROUP BY t.ID, v.ACTUAL_DATE;

Upvotes: 1

Related Questions