Well, I finally wrote an SQL query which returns for each ticket, the creation time, the first "SendAnswer" event time and the time diff between those two. It also shows the first response time value from the queue settings, so that you can compare real time and expected time (last two fields of the query). Here is the query:
select queue.name queue, service.name service, ticket.tn ticket, ticket.create_time, min(ticket_history.create_time) as resp_time, timestampdiff(second, ticket.create_time, ticket_history.create_time)/60 resp_delay_min, queue.first_response_time from queue, ticket, service, ticket_history, ticket_history_type where ticket.queue_id = queue.id and ticket.service_id = service.id and ticket_history.ticket_id = ticket.id and history_type_id = ticket_history_type.id and ticket_history_type.name = 'SendAnswer' group by queue.name, service.name, ticket.tn, ticket.create_time, queue.first_response_time The main problem with this query is that it does not take care of open hours and calendars. Since these informations are not stored in the database but in the config files, I don't know how to do. A good idea anybody ? Regards, Regis -----Message d'origine----- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Ali, Mustaqil M Envoyé : vendredi 25 avril 2008 12:24 À : User questions and discussions about OTRS.org Objet : Re: [otrs] SQL query for first response time and resolution time Hi, I'm pretty sure you could do this with data from the ticket_history table. Select based on the ticket_id, and then based on the history_type_id (which in turns relies on the ticket_history_type table), you should be able to build up dates of when the ticket was received, replied to, and closed indefinitely/last. >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of >Régis OBERLE >Sent: 16 April 2008 13:09 >To: [email protected] >Subject: [otrs] SQL query for first response time and resolution time > >Hello everybody, > >I want to build a report showing first response time and resolution >time. >The stat module doesn't seem to be able to do this. >So, I started using an external reporting tool. The hardest part is to >build the SQL query. >Can anybody help me finding these informations in the database ? >Do I have to compute these informations from "start" and "end" fields, >or is the data stored in the database (maybe the <ticket> table) ? >Thank you for your help. > >Regards, > >Régis >_______________________________________________ >OTRS mailing list: otrs - Webpage: http://otrs.org/ >Archive: http://lists.otrs.org/pipermail/otrs >To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >Support or consulting for your OTRS system? >=tp://www.otrs.com/ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? =http://www.otrs.com/ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/
