osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now
Calculate Average Time Between Statuses
I am not very good at SQL, but was able to write 80% of the queries needed for a report I am working on. Unfortunately, I am now stuck and looking for help in writing two queries. I would be happy to pay for the effort:
(I have six statuses, 4 are open, and 2 are closed)
1) Calculate the average time in days that a ticket with a status X has had status of X.
2) For all closed tickets, calculate the average time in days that a ticket had a status of X.
Part of the challenge is that the sca_ticket_thread table does not add a row when a ticket is initially created with title "Status Changed" and body explaining that the ticket was changed to X. I've come close to writing the first query, but it only works for status 1 (see below):
SELECT t3.status_id, ROUND(AVG(DATEDIFF(NOW(),t1.created))) days FROM `sca_ticket_thread` t1
JOIN (SELECT ticket_id,max(id) second_id FROM sca_ticket_thread WHERE title="Status Changed" GROUP BY ticket_id) t2
JOIN sca_ticket t3 ON t3.ticket_id = t1.ticket_id
GROUP BY t3.status_id