/build/static/layout/Breadcrumb_cap_w.png

Average Time to close tickets

I found this great link on how to create the report
https://support.software.dell.com/k1000-systems-management-appliance/kb/111916
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
  • Hi cjohnson

    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 7 years ago

Answers (1)

Posted by: Olendis 7 years ago
Orange Belt
0
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).

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ