I have managed to get a report together so that I can put it in Excel and get some graphs. I combined the Closed by Owner, Category, and Resolution to get the data into csv. One thing remaining would be to measure the time (duration) that the ticket was Open so that I can compare it to the SLA of the support folks. Currently the report displays the format "0d 1h 21m" whereas I just need numeric minutes so I can calculate the metrics (80% need to be closed in 2 hours, 95% in 1 Business day). Below is the query as it stands. I appreciate your help. If someone wants to take on the 2 metrics, I'll buy them a beer!

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') as TIME_OPENED,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_TICKET.RESOLUTION as RESOLUTION,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Just add this as a column. It will give you the time to close the ticket in minutes.
(TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) AS MIN_TO_CLOSE,
Answered 09/07/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
2
You could add these as the metric columns.
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 120,'Yes','No') AS 2_HOUR_SLA,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 1440,'Yes','No') AS 24_HOUR_SLA,
Answered 09/07/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
UR Awesome! 1 Whiskey, 1 Bourbon, 1 Beer for you!!
Answered 09/08/2011 by: Transam
Orange Belt

  • Could you paste your fix? I am not very familiar with SQL and having a rough time trying to find where the syntax should go. Thanks in advance!
Please log in to comment
Answer this question or Comment on this question for clarity