Count and group of a date column

mooriginal

Hi
I have the need to do a count of all open calls daily grouped for the hour

So for example:

10-11:00 - 5 open calls.
11-12:00 - 10 open calls

I have created the following that works to an extent but not like above.

select
CONVERT(VARCHAR(13), datumaangemeld, 120) Date_Hour,
COUNT(*) count_Records

FROM dbo.incident

WHERE ref_actiedoordynanaam = 'smith, david' and gereed = 0

GROUP By CONVERT(VARCHAR(13), datumaangemeld, 120)

Its producing results of :
Date_Hour Count
2008-09-09 15 1
2008-09-09 16 1
2008-09-09 18 1
2008-09-10 12 1
2008-09-11 12 1

Which is not ideal
Not sure how to get what I want

Any help appreciated ...

Thanks

Mo

Sep 12 '08 #1

amitpatel66

Couple of Questions regarding your requirement:

1. You should be GROUPING BY date-month-year-HR incase if you have same date of differnt years to avoid incorrect results
2. You said you want hourly count,so in case if the time is 11.15 then this call will be counted in !!o clock calls or 12 o clock calls? I think you should be looking at 1 HR range to find the COUNT of calls,something like 11-12,12-13 and so on?

Sep 15 '08 #2