asked
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)
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)))
Thanks! I did drop this into my TSQL. The datepart worked exactly the way I was looking for. I received a complaint with the cast ... as date, "Type date is not a defined system type" and discovered the tables are on SQL Server 2000. I'm guessing SQL 2000 doesn't accomodate this? I was able to use the following, which I found in a predecessor's queries: CONVERT(CHAR(23), A.date_t, 101)
14 People are following this question.
