alexandre alexandre - 4 months ago 8
SQL Question

Sql Grouping 3 rows of 5 minutes into a single one of 15 minutes

I want to Group 3 rows of 5 minutes into a single one of 15 minutes and be able to do an update on the 3 row if I want.

Here's an example:

2016-07-29 14:00:00.000 2016-07-29 14:05:00.000
2016-07-29 14:05:00.000 2016-07-29 14:10:00.000
2016-07-29 14:10:00.000 2016-07-29 14:15:00.000
2016-07-29 14:15:00.000 2016-07-29 14:20:00.000
2016-07-29 14:20:00.000 2016-07-29 14:25:00.000
2016-07-29 14:25:00.000 2016-07-29 14:30:00.000
2016-07-29 14:30:00.000 2016-07-29 14:35:00.000
2016-07-29 14:35:00.000 2016-07-29 14:40:00.000
2016-07-29 14:40:00.000 2016-07-29 14:45:00.000
2016-07-29 14:45:00.000 2016-07-29 14:50:00.000
2016-07-29 14:50:00.000 2016-07-29 14:55:00.000
2016-07-29 14:55:00.000 2016-07-29 15:00:00.000
2016-07-29 15:00:00.000 2016-07-29 15:05:00.000


And the result should be :

2016-07-29 14:00:00.000 2016-07-29 14:15:00.000
2016-07-29 14:15:00.000 2016-07-29 14:30:00.000
2016-07-29 14:30:00.000 2016-07-29 14:45:00.000
2016-07-29 14:45:00.000 2016-07-29 15:00:00.000
...


Thank you! :)

Answer

Basically, you want to truncate each time to 15-minute intervals and use that for aggregation. Something like this:

select distinct dateadd(minute, datediff(minute, 0, col1) / 15 * 15, 0),
       dateadd(minute, datediff(minute, 0, col1) / 15 * 15 + 15, 0)