Report for Average Time to Respond to Tickets by User for Past 7 Days
Hi Guys,
I'm working on creating a report to show the Average Time to Respond to Tickets by each User for the Past 7 Days.
I'm currently working on extracting the main data I need to achieve this.
Essentially, I am trying to collect the Ticket ID, Ticket Creation Time, 1st Action Time, and 1st Action User.
I am considering the 1st Action Time to be the 1st entry in the HD_TICKET_CHANGE table for each Ticket ID where the DESCRIPTION does not equal 'Ticket Created'.
This is my current SQL query to get this date:
SELECT HD_TICKET.ID as TicketID, HD_TICKET.CREATED as CreatedDateTime, HD_TICKET_CHANGE.TIMESTAMP as 1stActionDateTime, HD_TICKET_CHANGE.USER_ID as 1stActionedBy
FROM HD_TICKET
JOIN HD_TICKET_CHANGE
WHERE HD_TICKET.HD_QUEUE_ID = 1
AND HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.DESCRIPTION != "Ticket Created"
ORDER BY HD_TICKET_CHANGE.TIMESTAMP
Now this query works to the point of outputting all the rows from the HD_TICKET_CHANGE table that have HD_TICKET_ID matching HD_TICKET.ID and DO NOT CONTAIN "Ticket Created" in the DESCRIPTION field.
What I need however is the 1st entry in the HD_TICKET_CHANGE table (1st entry to be determined by it's TIMESTAMP entry being the oldest) WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.DESCRIPTION != "Ticket Created".
This will allow me to only output the 1st change on each ticket.
I'm currently stumped as to how I can modify this query to do this. I'm hoping someone here will have an idea of how I can achieve this.
Thanks Guys
Best Regards
Dave
I'm working on creating a report to show the Average Time to Respond to Tickets by each User for the Past 7 Days.
I'm currently working on extracting the main data I need to achieve this.
Essentially, I am trying to collect the Ticket ID, Ticket Creation Time, 1st Action Time, and 1st Action User.
I am considering the 1st Action Time to be the 1st entry in the HD_TICKET_CHANGE table for each Ticket ID where the DESCRIPTION does not equal 'Ticket Created'.
This is my current SQL query to get this date:
SELECT HD_TICKET.ID as TicketID, HD_TICKET.CREATED as CreatedDateTime, HD_TICKET_CHANGE.TIMESTAMP as 1stActionDateTime, HD_TICKET_CHANGE.USER_ID as 1stActionedBy
FROM HD_TICKET
JOIN HD_TICKET_CHANGE
WHERE HD_TICKET.HD_QUEUE_ID = 1
AND HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.DESCRIPTION != "Ticket Created"
ORDER BY HD_TICKET_CHANGE.TIMESTAMP
Now this query works to the point of outputting all the rows from the HD_TICKET_CHANGE table that have HD_TICKET_ID matching HD_TICKET.ID and DO NOT CONTAIN "Ticket Created" in the DESCRIPTION field.
What I need however is the 1st entry in the HD_TICKET_CHANGE table (1st entry to be determined by it's TIMESTAMP entry being the oldest) WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.DESCRIPTION != "Ticket Created".
This will allow me to only output the 1st change on each ticket.
I'm currently stumped as to how I can modify this query to do this. I'm hoping someone here will have an idea of how I can achieve this.
Thanks Guys
Best Regards
Dave
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
davids
12 years ago
Hi Guys,
I've managed to find a solution to this and complete the query.
I thought i'd throw it up here in case anybody else wanted to take advantage of this report.
This is a report to get the average initial response time to tickets for the last 7 days grouped by user.
You can easily change the time the amount of days reported on by modifying the 2 'SUBTIME(NOW(), '7 00:00:00')' functions.
Cheers
Dave
I've managed to find a solution to this and complete the query.
I thought i'd throw it up here in case anybody else wanted to take advantage of this report.
This is a report to get the average initial response time to tickets for the last 7 days grouped by user.
You can easily change the time the amount of days reported on by modifying the 2 'SUBTIME(NOW(), '7 00:00:00')' functions.
SELECT DATE_FORMAT(SUBTIME(NOW(), '7 00:00:00'), '%b %d %Y %H:%i') as 'Date From',
DATE_FORMAT(NOW(), '%b %d %Y %H:%i') as 'Date To',
RESULT.ActionedBy as 'User',
CAST(SEC_TO_TIME(AVG(RESULT.TimeToAction)) AS CHAR(255)) as 'Average Response Time'
FROM (SELECT T.ID as 'TicketID',
TIMEDIFF(C.TIMESTAMP,T.CREATED) as 'TimeToAction',
(SELECT U.FULL_NAME FROM USER U WHERE C.USER_ID = U.ID) as 'ActionedBy'
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C
WHERE T.HD_QUEUE_ID = 1
AND T.CREATED >= SUBTIME(NOW(), '7 00:00:00')
AND C.HD_TICKET_ID = T.ID
AND C.DESCRIPTION != 'Ticket Created'
GROUP BY T.ID
ORDER BY T.ID) RESULT
GROUP BY RESULT.ActionedByCheers
Dave
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.