@rothgar7
I think I confused you by mentioning the event table. The ost_event
table just lists the different types of events like created
, closed
, reopened
, etc. Let's say an ost_thread_event
record has an event_id
of 3
this means the thread event was a reopened event. The ost_event
table just helps us maintain a dynamic list of thread event types easier.
If you want to manually run the query, the query would be:
SELECT
AVG(timestampdiff(HOUR, A3.`timestamp`, A1.`timestamp`)) AS `ServiceTime`,
AVG(timestampdiff(HOUR, A5.`created`, A4.`created`)) AS `ResponseTime`
FROM `ost_thread_event` A1
JOIN `ost_thread` A2 ON (A1.`thread_id` = A2.`id`)
LEFT JOIN `ost_thread_event` A3 ON (A2.`id` = A3.`thread_id` AND (A3.`event_id` = 1 AND A1.`event_id` = 2 AND A1.`annulled` = 0))
LEFT JOIN `ost_thread_entry` A4 ON (A2.`id` = A4.`thread_id` AND A4.`type` = 'R')
LEFT JOIN `ost_thread_entry` A5 ON (A4.`pid` = A5.`id`)
WHERE A1.`timestamp` BETWEEN FROM_UNIXTIME(1638316800) AND FROM_UNIXTIME(1641237757);
Note:
1638316800
is the date and time to start in Unix (ie. December 1, 2021 12:00:00 AM UTC
)
1641237757
is the date and time to end in Unix (ie. January 3, 2022 7:22:37 PM UTC
)
So reviewing the query you can see that it grabs all ost_thread_event
records and grabs the related ost_thread
records for each. It uses this information for the remaining left joins.
For ServiceTime it calculates the average time difference in hours between the timestamp
value for the thread event record with event type of created
(ie. A3.event_id = 1
) and the timestamp
value for the thread event record with event type of closed
(ie. A1.event_id = 2
). Essentially the average amount of time in hours agents were able to close opened tickets.
For ResponseTime it calculates the average time difference in hours between the created
value for the parent thread entry (ie. A4.pid = A5.id
) and the created
value for the subsequent reply thread entries (ie. A2.id = A4.thread_id AND A4.type = 'R'
). Essentially the average amount of time in hours agents were able to respond to new user messages.
Cheers.