First response time between ticket open and ticket response

[nickwilliams999]

Hi,

In the interest of sharing, I just thought I would share some code I have written to find out the amount of time it took to respond to a ticket, excluding and auto-response.

SELECT t.id AS TicketID,
t.title,
t.message,
t.status,
t.urgency,
t.date, ttr.tid, ttr.message, MIN(ttr.date) AS TicketReplyDate,
TIMEDIFF(ttr.date, t.date) AS FirstResponseTime
FROM tblticketreplies ttr
JOIN tbltickets t ON ttr.tid = t.id
WHERE ttr.message NOT LIKE '%Thanks for reaching out!%'
GROUP BY ttr.tid ASC

In addition to this, I also converted the FirstResponseTime to an integer within my reporting tool, this was outside of the SQL code:

FirstResponseDurationInMinutes = DATEDIFF('Support Ticket Response Times'[date], 'Support Ticket Response Times'[TicketReplyDate], MINUTE)

FirstResponseDurationInHours = 'Support Ticket Response Times'[FirstResponseDurationInMinutes] / 60

This is certainly a requirement in KPI reports we run, so hopefully someone finds this useful!

Nick