Reporting query and help
I would like a report that shows me the average time each technician has spent on requests by priority. I am failry sure the query below does this to get it I
- Created a Matrix Report for requests
- Showed technician down the left side, priority across the top and Summarized columns by average of Time Spent
- I then filtered to show only completed requests for last month
This is the query I get when I show query from the result. Can anyone
- Confirm to me that the results should show in hh:mm:ss the average time each technician has spent completing requests for each priority
- Edit this query to show only time during business hours (8:30am to 5:00pm Mon-Fri). So for a request that is logged on 4:55pm Friday and closed at 9:00am Monday I want that to count as 35 minutes, not the whole weekend.
SELECT pd.PRIORITYNAME 'Priority',ti.FIRST_NAME 'Technician',wo.TIMESPENTONREQ 'Time Spent' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((( std.STATUSNAME = 'Closed') AND ((wo.COMPLETEDTIME >= $P{fromdate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND ((wo.COMPLETEDTIME <= $P{todate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))))
- Created a Matrix Report for requests
- Showed technician down the left side, priority across the top and Summarized columns by average of Time Spent
- I then filtered to show only completed requests for last month
This is the query I get when I show query from the result. Can anyone
- Confirm to me that the results should show in hh:mm:ss the average time each technician has spent completing requests for each priority
- Edit this query to show only time during business hours (8:30am to 5:00pm Mon-Fri). So for a request that is logged on 4:55pm Friday and closed at 9:00am Monday I want that to count as 35 minutes, not the whole weekend.
SELECT pd.PRIORITYNAME 'Priority',ti.FIRST_NAME 'Technician',wo.TIMESPENTONREQ 'Time Spent' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((( std.STATUSNAME = 'Closed') AND ((wo.COMPLETEDTIME >= $P{fromdate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND ((wo.COMPLETEDTIME <= $P{todate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))))