Hello,

 

I am using the following report as a template for this...I have pulled it from the KACE KB. This works great as a way to track average ticket time by queue however for incentives I need this to track by ticket owner.

 

Any help on this would be great!

 

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>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*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>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP


Thanks,

 

Anthony

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I get "incorrect datetime value" and "truncated incorrect time value" when I run the query above, but in case this might help (i.e. give you something to work with) - here's the query with a column added for ticket owners and the results grouped on the owners.  Something to play with, I guess.

Hope that helps!

John

SELECT USER.USER_NAME as OWNER,
(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
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
UNION
select
USER.USER_NAME as OWNER,
'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
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
Answered 10/08/2012 by: jverbosk
Red Belt

  • Confirmed working by vmescall per:

    http://www.itninja.com/question/custom-report-average-initial-response-time-grouped-by-technician-for-last-28-days

    John
  • Thanks!
Please log in to comment
Answer this question or Comment on this question for clarity