First response time

rmcgeown

Hi all,

I have a requirement to measure the time between ticket open and first reply.

I can see that in the ost_ticket_response table all the replies are recorded, but I do not know how to identify the first reply on a ticket and subsequently calculate the time between the two in business hours (08 - 17 Mon - Fri).

equisde

Hi,

First of all you should get the ticket_id (internal key on database, not the public TicketID) from ost_ticket table. You could get it with a sql sentence like this:

SELECT ticket_id from ost_ticket WHERE ticketID = (your target public TICKET ID)

Once done you should obtain the first two responses for that ticket_id at ost_ticket_response table. First one will be the original message and the second one will be the first response. You could do it with another SQL sentence like this:

SELECT TOP 2 from ost_ticket_response WHERE ticket_id = (ticket_id obtained in the last step) ORDER BY created ASC

NOTE: If you're using MySQL your sql sentence will be:

SELECT * FROM ost_ticket_response WHERE ticket_id = (ticket_id obtained in the last step) ORDER BY created ASC LIMIT 2

When you have got this two records you can calculate the time between both records by comparing the created date. My suggestion is to use DATEDIFF function

ntozier

I think that you could also replace the second two queries in equisades post with something like the following:

SELECT ost_ticket_response.ticket_ID, ost_ticket_response.created, DATEDIFF(ost_ticket_response.created, t2.created) as diff

FROM ost_ticket_response

LEFT JOIN ost_ticket_response AS t2 ON t2.ticket_ID = ost_ticket_response.ticket_id - 1

WHERE ost_ticket_response.ticket_id = (ticket_id obtained in the last step)

ORDER BY created ASC LIMIT 1

(You would still have customize the query to give you days, hours, minutes, seconds)

equisde

I think that you could also replace the second two queries in equisades post with something like the following:
SELECT ost_ticket_response.ticket_ID, ost_ticket_response.created, DATEDIFF(ost_ticket_response.created, t2.created) as diff
FROM ost_ticket_response
LEFT JOIN ost_ticket_response AS t2 ON t2.ticket_ID = ost_ticket_response.ticket_id - 1
WHERE ost_ticket_response.ticket_id = (ticket_id obtained in the last step)
ORDER BY created ASC LIMIT 1
(You would still have customize the query to give you days, hours, minutes, seconds)

+1 Better than mine! I wrote it in two sentences just for clarifying, but join the sentences is the optimal way.

RiosQ

equisde Hi! has this worked for you?

KevinTheJedi

RiosQ

This is super old...their last reply was 11 years ago. Please do not revive zombie threads. Since it's 11 years old I doubt this will work in latest releases; although you can try at your own risk.

Cheers.