SQL Server - query by hour, over multiple days

Question

I am looking to run a SQL query using datepart to split data into hourly increments, and then make that query run over multiple days. Currently I just increment the dates by a day and then run multiple queries. I'm wondering if there's a way to consolidate this all into one query that will give output for an entire date range.

select datepart(hour, datetime), sum(calls)
from dbo.gateway_concurrent_calls
where datetime between 'MM/DD/YY' and 'MM/DD+1/YY'
group by datepart(hour, datetime)

This gives this as output.

0   1220
1   569
2   391
3   313
4   161
5   68
6   42
7   24
8   19
9   18
10  23
11  45
12  90
13  311
14  566
15  668
16  688
17  735
18  718
19  729
20  701
21  699
22  683
23  570

Answer 1

Group by both the date AND the hour:

select convert(date, datetime), datepart(hour, datetime), sum(calls)
from dbo.gateway_concurrent_calls
where datetime between 'MM/DD/YY' and 'MM/DD+1/YY'
group by convert(date, datetime), datepart(hour, datetime)

Answer 2

This query will round the datetime column to the nearest hour, whilst keeping the date portion also, giving the results you seek, I believe:

select DATEADD(hour,DATEDIFF(hour,0,datetime),0), sum(calls)
from dbo.gateway_concurrent_calls
where datetime between @StartDate and @EndDate
group by DATEADD(hour,DATEDIFF(hour,0,datetime),0)

Note that I've assumed the start and end dates will be supplied as variables/parameters - if you're going to continue to use literal strings, try to use the format YYYYMMDD (for dates), e.g. today is 20111129 - this format is unambiguous across regional settings for SQL Server.