I'm trying to come up with a query that gets an hourly count of records from a specific table on a per day basis. In other words, I have a table called ImportToLeadCenterLog. In this table is a date time field called importDate. What I want is to get a count of all the records that happened in hour1, hour2, hour3, etc. So if there were 2 records that had a importDate of '8/15/2007 3:35:00' and '8/15/2007 3:56:00' respectively, the count for hour3 would be two. I would also like to have it for a given range of days, so the where clause would be:
where importDate >= '8/15/2007' and importDate <= '8/18/2007'
So far I have the following, which works, but only returns 1 record for whatever date range I enter:
sum(case when DatePart(hh,IMPORTDATE)=1 then 1 else 0 end) As countHour1,
sum(case when DatePart(hh,IMPORTDATE)=2 then 1 else 0 end) As countHour2,
sum(case when DatePart(hh,IMPORTDATE)=3 then 1 else 0 end) As countHour3,
sum(case when DatePart(hh,IMPORTDATE)=4 then 1 else 0 end) As countHour4,
'up to hour 24
from ImportToLeadCenterLog where
importDate > '8/12/2007 00:00:00' and
importDate <= '8/15/2007 23:59:59'