Average Time to close tickets
I found this great link on how to create the report
I want to break it down into smaller amounts of data. I would like to be able to view the report by technician's, and view it by weeks or by month.
I am not good with SQL in any way. So all of this is new to me
Any help with creating this report would be great
Also would there be a way to show subcategoriesÂ
Thank you
1 Comment
[ + ] Show comment
Answers (1)
Please log in to answer
Posted by:
Olendis
8 years ago
Hi,
You can try this
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 Selection,
count(HD_TICKET.ID) as Nombre_de_ticket_des_30_derniers_jours,
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 Temps_de_traitement_moyen_par_ticket 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>'2007-01-01' /*change the start date here*/
and TIME_CLOSED<'2008-04-30' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by Selection
UNION
select 'Tous les tickets' as Selection,count(HD_TICKET.ID) as Nombre_de_ticket_des_30_derniers_jours,
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 Temps_de_traitement_moyen_par_ticket 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>'2016-04-01' /*change the start date here*/
and TIME_CLOSED<'2050-12-25' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
and DATEDIFF(NOW(), CREATED) < 30
group by Selection
I am french so my column are explain in french.
This SQL do what you want for my K1000 for the last 30 days (not the month).
I created this report for the company I am working for...
SELECT Round((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, TIME_OPENED))/3600)/(select count(id) from HD_TICKET as hdticket2 where hdticket2.OWNER_ID=hdticket1.OWNER_ID)),3) as 'MTBC(Hours)', USER.USER_NAME as Username
FROM HD_TICKET as hdticket1
LEFT JOIN USER ON hdticket1.OWNER_ID = USER.ID
LEFT JOIN HD_STATUS ON hdticket1.HD_STATUS_ID = HD_STATUS.ID
where month(hdticket1.TIME_CLOSED) = month(current_date-interval 1 month)
and year(hdticket1.TIME_CLOSED) = year(now())
AND STATE like '%close%'
AND hdticket1.HD_QUEUE_ID IN (1,3,4,9)
GROUP BY Username ASC WITH ROLLUP
So what it basically does is to round and sum the calculated difference (in seconds) between the hd_ticket.time_closed and hd_ticket.time_opened and then divide it by 3600 to convert it to hours.
Then I select the username for the technician and set a filter to select only tickets that were closed in the previous calendar month and from only 4 queues.
I want the previous calendar month because we run our KPI on the first of every month. - Myth0S 8 years ago