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

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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...
Please log in to comment

Answers

0
I've written an update to the standard recommended Dell KB query on this topic. It takes this same approach but gives you better closure bands and accounts for weekends and holidays. I've written it up here http://www.itninja.com/blog/view/k1000-service-desk-a-better-average-time-to-close-report
Answered 01/07/2016 by: cnilsson
Senior White Belt

Please log in to comment
0

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;

Answered 12/12/2013 by: ammaross
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity