K1000: Average response time for a specific queue
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!
Answers (3)
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
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
;
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
;