SQL Reporting - Average Ticket Time Opened
Hi,
I found this report in another thread and it does exactly what I want it to do, except I don't have anything in the Custom Field 13 so the "Average Time Opened" part of this report does not work. What do I need to do/add to the Custom Field 13 to make this report work correctly? Thanks in advance!
SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS, AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'
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.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
This query gets the time a ticket is open by subtracting the TIME_OPENED from TIME_CLOSED and then formats it in a human readable format:
SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
TIME_FORMAT(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND,
TIME_OPENED,
TIME_CLOSED)
)),'%Hh %im') AS "Average Time"
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.NAME='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
Comments:
-
That worked perfectly, Exactly what i needed!! Thank you! - BDEEN 9 years ago
Posted by:
chucksteel
9 years ago
Whoever created this report must have had a custom rule in place to keep the time opened in the custom field. I believe you can use AVG(HD_TICKET.TIME_OPENED) instead but I don't have access to the database to check that at the moment.
Comments:
-
Using that does return a value for Average Time, but it looks like this: 17050262763383.0000
Is there a way to put that in a Minute/Day/Hour format? - BDEEN 9 years ago
