# question

## Aggregate datetime from 15-minute increments to single hour

I am trying to figure out the best way to aggregate following as a single hour with a total. The result set, in turn needs to be selected both by day and by hour. The following data is an example of what needs to be grouped and totaled as a total for the 10:00 hour. StoreId | date_t | Visitors 480 | 2013-11-11 10:15:00.000 | 363 (10:00-10:15) 480 | 2013-11-11 10:30:00.000 | 404 (10:15-10:30) 480 | 2013-11-11 10:45:00.000 | 300 (10:30-10:45) 480 | 2013-11-11 11:00:00.000 | 319 (10:45-11:00)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Apologies, the parentheses are mine, not from the data.
0 Likes 0 ·

·
date_t is storing the upper bound of the 15-minute increment, so by subtracting 15 minutes, you'll get the time in the 'right' hour. Then it's a simple aggregate over that and the date declare @yourtable table ( storeid int, date_t datetime, visitors int ) insert into @yourtable select 480, '2013-11-11 10:15:00.000', 363 insert into @yourtable select 480, '2013-11-11 10:30:00.000', 404 insert into @yourtable select 480, '2013-11-11 10:45:00.000', 300 insert into @yourtable select 480, '2013-11-11 11:00:00.000', 319 select cast(date_t as date), datepart(hour,(dateadd(minute, -15, date_t))), sum(Visitors) as HourlyVisitors from @yourtable group by cast(date_t as date), datepart(hour,(dateadd(minute, -15, date_t)))