Search code examples
sql-servercrystal-reportsaverage

Crystal Report for Average Tickets revived by hour per month

I need to pull a report that show the avg tickets we receive per hour for a month.

So far I've been able to come up with: 

 SELECT "TASKS"."WO_NUM", "TASKS"."TASK", "TASKS"."COMPLETED", "TASKS"."OPENDATE", 
"TASKS"."REQUEST", "TASKS"."RESPONS"
     FROM   "TRACKIT9_DATA"."dbo"."TASKS" "TASKS"
     WHERE  ("TASKS"."OPENDATE">={ts '2015-09-01 00:00:00'} AND
 "TASKS"."OPENDATE"<{ts '2015-09-04 00:00:00'})

However this only show the number of ticket we received each hour per day and the report is a mess if I ask for a full months worth of data.

How would I go about asking Crystal Reports to Pull the data then Group it by Hour then give me an average?

Solution

  • You can do this in crystal report.

    Group by month then create a formula and write as

    Hour(database date field)

    Use this to group as a second group after month, now try to manipulate the data