Hi Gabriele, are you looking (a) for the gap between ticket-creation and -closure? This could be easily calculated from DB-entries if ticket-closure-time would be a singulare event, but tickets can always be reopened. Even if you configure otrs that the customer can't do that, the agent can always reopen a ticket. (b) for the time-units a agent reported for working on the ticket? select t.id, t.tn, (select sum(time_unit) from time_accounting where ticket_id = t.id) as sum_time_units from ticket t (I haven't verified this query) Bye, Alex Gabriele D'Andrea schrieb:
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