Calls per hour between Date/Time

[Dross]

I have a simple report that needs to count how many calls were made per hour grouped by hour. In other words I have 3 calls. One was 1/7/03 11:32, another was 1/7/03 11:36 and the third was 1/7/03 11:44. I want Crystal to show from 1/7/03 11:00 am to 1/7/2003 12:00 pm there were 3 calls. I can do the SQL for it and run it on the server, but I need it in Crystal. I can't get it even close. Any clues?

The SQL is:
Select Count(convert(varchar(13),Starttimeofcall, 121)), convert(varchar(13),Starttimeofcall, 121) from Calls
group by convert(varchar(13),Starttimeofcall, 121)
order by convert(varchar(13),Starttimeofcall, 121) desc

 

[lyanch]

Do you need to show hours that don't have any calls? It is relatively easy if you don't need to show hours that don't have calls.

Group by day first then by datepart("h",YourDatetimefield)

You can count the records in the detail without displaying it.

Lisa

 

[ptpenry]

If you need zeros I would probably use the SQL CASE statement. It would be something like ( I can't check at the moment as I don't have SQL on this PC )

SELECT

COUNT(CASE WHEN DATEPART("h",starttimeofcall)=0 THEN 1 END) AS AM12till1,

COUNT(CASE WHEN DATEPART("h",starttimeofcall)=1 THEN 1 END) AS AM1till2

etc

If you want it for more than one day, you could group by the date part of your field. Each record would then have 25 fields; the date and 1 figure per hour.

You could save the query as a SQL stored procedure then base your Crystal report on the stored proc - we tend to use stored procs a lot as doing the filtering on SQL Server and just returning the records you need is far more efficient than Crystal doing it.

Hope this helps