troubleshooting Question

get hourly count of records on a per day basis

Avatar of Big Monty
Big MontyFlag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution2208 ViewsLast Modified:
heya experts,
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:

select
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'

TIA
Josh
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros