question

mahler9 avatar image
mahler9 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)
t-sqldatetimeaggregatescast-convert
1 comment
10 |1200 characters needed characters left characters exceeded

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 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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)))
2 comments
10 |1200 characters needed characters left characters exceeded

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

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)
0 Likes 0 ·
Again, thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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