Report for initial response time for closed helpdesk tickets

Notes:

• only considers closed tickets. Tickets that were closed but reopened do not count if they are currently open or stalled.
• A valid response is:
  • the first comment on a ticket that is made by anyone who is in the helpdesk owner label for the queue of interests unless that person is also the submitter
  • can be empty

How To Use

• Open the SQL editor for creating a report (http://k1000/adminui/report.php) Note: you cannot use this report in the graphical view
• fill our relevant fields of the form
• Paste the SQL in the SQL area
• click on "auto-generate layout"
• save report
• Run the HTML (or other versions) of the report

Query

select
HD_CATEGORY.NAME CATEGORY,
HD_TICKET.ID TICKET,
HD_TICKET.CREATED,
HD_TICKET_CHANGE.TIMESTAMP RESPONSETIME,
RESPONDER.USER_NAME RESPONDER,
CONCAT(( sum( TIME_TO_SEC( TIMEDIFF( HD_TICKET_CHANGE.TIMESTAMP, IF(HD_TICKET.CREATED='0', HD_TICKET_CHANGE.TIMESTAMP,HD_TICKET.CREATED)
)
)
)/count(HD_TICKET.id) div 86400
),'d ',
TIME_FORMAT(
SEC_TO_TIME(
sum(
TIME_TO_SEC(
TIMEDIFF(
HD_TICKET_CHANGE.TIMESTAMP,
IF(HD_TICKET.CREATED='0', HD_TICKET_CHANGE.TIMESTAMP,HD_TICKET.CREATED)
)
)
)/count(HD_TICKET.id) mod 86400
),'%kh %im %ss'
)) as TIME_TO_RESPOND,
TIME_TO_SEC(TIMEDIFF(HD_TICKET_CHANGE.TIMESTAMP,IF(HD_TICKET.CREATED='0', HD_TICKET_CHANGE.TIMESTAMP,HD_TICKET.CREATED))) SECS_TO_RESPOND,
CONCAT('http://support.corp.convio.com/adminui/ticketphp?ID=',HD_TICKET.ID) URL,
HD_TICKET_CHANGE.ID

FROM
HD_TICKET,
HD_TICKET_CHANGE,
HD_STATUS,

( SELECT min(HD_TICKET_CHANGE.ID ) FIRSTID,HD_TICKET_ID FROM HD_TICKET,HD_TICKET_CHANGE , USER, USER_LABEL_JT, HD_QUEUE_OWNER_LABEL_JT
   WHERE HD_TICKET.ID=HD_TICKET_CHANGE.HD_TICKET_ID and HD_TICKET.SUBMITTER_ID<> HD_TICKET_CHANGE.USER_ID and HD_TICKET_CHANGE.OWNERS_ONLY=0 and HD_TICKET_CHANGE.USER_ID=USER.ID and HD_TICKET_CHANGE.USER_ID=USER_LABEL_JT.USER_ID and USER_LABEL_JT.LABEL_ID = HD_QUEUE_OWNER_LABEL_JT.LABEL_ID and HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID = HD_TICKET.HD_QUEUE_ID and HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID IN (1) /*****look at main queue******/  and HD_TICKET_CHANGE.COMMENT<>''
GROUP BY HD_TICKET_ID) HTC,
HD_CATEGORY,
USER RESPONDER

WHERE
HD_TICKET.ID=HTC.HD_TICKET_ID and
HTC.FIRSTID=HD_TICKET_CHANGE.ID and
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and
HD_TICKET.HD_CATEGORY_ID=HD_CATEGORY.ID and
HD_TICKET_CHANGE.USER_ID=RESPONDER.ID and
HD_STATUS.NAME like '%closed%' /*** all with status containing closed***/

GROUP BY HD_TICKET.ID
ORDER BY HD_CATEGORY.NAME ASC,TIME_TO_RESPOND DESC

.