@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.
