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