Kace Average Ticket Time Grouped by Owner and Interval
Hello. I have created an SQL report in Kace that shows average ticket times group by 24 hour intervals. Above the time interval I would like to group by owner so we can report on each technician and how long that individuals average ticket time is. Anyone know how to go about that?!
SELECT
(case when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),
INTERVAL 1 HOUR) then '0-1 hour'
when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL
24 HOUR) then '1-24 hours'
when
TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL
24 HOUR) then '>24 hours'
else 'error' end ) as CLOSE_GROUP,
count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET,
HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2013-01-01' /*change the start date here*/
and TIME_CLOSED<'2013-12-31' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP
UNION
select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET
,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2013-01-01' /*change the start date here*/
and TIME_CLOSED<'2013-12-31' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP
-
What does the error mean under the Close group Column and can you get rid of it ???? Also, is there a way to expand this to >48hrs and >72hrs... - wtomasiak 10 years ago
Answers (2)
This should accomplish what you need:
SELECT
USER.FULL_NAME,
(case when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 1 HOUR) then '0-1 hour'
when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'
when TIME_CLOSED>=DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'
else 'error' end ) as CLOSE_GROUP,
count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
from HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
where
HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2013-01-01' /*change the start date here*/
and TIME_CLOSED<'2013-12-31' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by HD_TICKET.OWNER_ID,CLOSE_GROUP
UNION
select
USER.FULL_NAME,
'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
from HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
where
HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2013-01-01' /*change the start date here*/
and TIME_CLOSED<'2013-12-31' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by HD_TICKET.OWNER_ID,CLOSE_GROUP;