Average Ticket Resolution Time by Owner (in days)
Trying to find a report that gives the same information that the new widget Average Ticket Resolution Time by Owner (in days) does. Tried creating my own but can't get the average days to come out correct and I want this for 30 day intervals. I don't want to have to keep changing beginning and end dates in my report every month.
1 Comment
[ + ] Show comment
-
I was trying to make it too complicated - scarpent 7 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
This should work, or at least get you close:
SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
ROUND((AVG(TIMESTAMPDIFF(DAY,
HD_TICKET.CREATED,
TIME_CLOSED)
)), 1) AS "Avg Days Open"
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.STATE ='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (2) /*add queue numbers here*/
GROUP BY OWNER
Note that this is for the specific queue specified in the query.
Comments:
-
Can you be more specific on *add queue numbers here*? SQL Noob here. - Charles.K 6 years ago
-
If you have created more than one queue that you open tickets in or that you have another department using. We did have 2 queues one for the IT Department and then one for our Print Services Department. - scarpent 6 years ago
