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
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
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

Answered 03/25/2015 by: chucksteel
Red Belt

  • That worked perfectly, Exactly what i needed!! Thank you!
Please log in to comment

Answers

0
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.

Answered 03/25/2015 by: chucksteel
Red Belt

  • 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?
Please log in to comment
Answer this question or Comment on this question for clarity

Share