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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

1
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.
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.ActionedBy


Cheers
Dave
Answered 09/04/2011 by: davids
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity