We are trying to create a report that shows response time to a ticket by priority and another by owner, but only for a specific queue. We would like to calculate the response time by the amount of time between the ticket creation and the ticket getting assigned to an owner, but I don't know enough SQL to generate this. Any help would be greatly appreciated!

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

While not a complete answer to your question, here is a link that I use commonly for reports on Service Desk queue close times.  It should provide a good spring board for getting you what you're looking for.

http://www.kace.com/support/resources/kb/article/Helpdesk-Tickets-By-Average-Time-to-Close

 

Answered 08/07/2013 by: ShawnCarson
White Belt

Please log in to comment
0

Here's a start in the right direction:

 

SELECT
AVG(TIME_TO_SEC(TIMEDIFF(SECOND_CHANGE.TIMESTAMP,INITIAL_CHANGE.TIMESTAMP))),
COUNT(INITIAL_CHANGE.ID)

FROM HD_TICKET

JOIN HD_TICKET_CHANGE HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID

JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(
select MIN(ID) from HD_TICKET_CHANGE
where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)

JOIN HD_TICKET_CHANGE SECOND_CHANGE ON SECOND_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and SECOND_CHANGE.ID=(
select MIN(ID) from HD_TICKET_CHANGE
where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.TIMESTAMP != HD_TICKET.TIME_OPENED
AND HD_TICKET_CHANGE.TIMESTAMP != INITIAL_CHANGE.TIMESTAMP
)

LEFT JOIN USER UPDATER ON UPDATER.ID = HD_TICKET_CHANGE.USER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

WHERE HD_TICKET_CHANGE.USER_ID != 0
AND HD_TICKET.HD_QUEUE_ID = 1

AND (INITIAL_CHANGE.TIMESTAMP LIKE "2012-09%"
OR INITIAL_CHANGE.TIMESTAMP LIKE "2012-10%"
OR INITIAL_CHANGE.TIMESTAMP LIKE "2012-11%")
AND SUBMITTER.ID != HD_TICKET_CHANGE.USER_ID
AND HD_TICKET_CHANGE.USER_ID != 4538
AND HD_TICKET_CHANGE.USER_ID != 5878
AND HD_TICKET_CHANGE.USER_ID != 3730
AND HD_TICKET_CHANGE.USER_ID != 5844
AND HD_TICKET_CHANGE.USER_ID != 5779
AND HD_TICKET_CHANGE.USER_ID != 5841
AND HD_TICKET_CHANGE.USER_ID != 4412
AND HD_TICKET_CHANGE.USER_ID != 5231
;

Answered 12/16/2013 by: tholmes
Green Belt

Please log in to comment
0

Actually this is much simpler, anyone know a good way to loop between the min and max tickets to calculate the average response time?

 

SELECT
MIN(HD_TICKET.ID) AS FIRST_TICKET,
HD_TICKET.CREATED,
SECOND_CHANGE.TIMESTAMP,
TIMEDIFF(SECOND_CHANGE.TIMESTAMP,HD_TICKET.CREATED) AS RESP_TIME,
MAX(HD_TICKET.ID) AS LAST_TICKET
 
FROM HD_TICKET_CHANGE

JOIN HD_TICKET HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID

LEFT JOIN HD_TICKET_CHANGE SECOND_CHANGE ON (SECOND_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and SECOND_CHANGE.ID=((
select MIN(ID) from HD_TICKET_CHANGE
where (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.TIMESTAMP != HD_TICKET.CREATED
AND HD_TICKET.SUBMITTER_ID != HD_TICKET_CHANGE.USER_ID))
))

where HD_TICKET_CHANGE.USER_ID != 0
AND HD_TICKET.HD_QUEUE_ID = 1
;

Answered 12/20/2013 by: tholmes
Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity