I'm trying to make a custom report that can tell me, for every ticket, the amount of time taken to close the ticket.

Since I can't find a module to do that, i'm trying to query mysql to extract data I need.

But I'm not sure it's possible to retrieve the exact date when a ticket was closed using only mysql.

Tickets are stored in the TICKET table, linked to the TICKET_HISTORY table, that stores all the events regarding the tickets (e.g. ticket close, follow-up, note etc.)

Then the TICKET_HISTORY table is linked to the TICKET_HISTORY_TYPE table, that stores all the possible events.

Since it's possible to edit the ticket after it's been closed, there are many occurences of CLOSED states in the ticket_history for the same ticket.

I should check for the action that made the ticket close, but it's not possible to determine which of the many ticket_history_type determined the closing.

I could select the first occurrence of CLOSED in TICKET_HISTORY, but what if the ticket was closed, then reopend, then closed again?

I my reasoning right or am I missing something?

--
Gabriele D'Andrea