Well, there was something I still didn't understand about OTRS DataBase Schema. Now finally I got it. I've written a SQL query, that allow me to filter closed tickets, and see the time taken from ticket opening till the last closing.
SELECT t.tn, t.customer_id, t.create_time, MAX(th.change_time) AS change_time, DATEDIFF(MAX(th.change_time), t.create_time) AS open_days, TIMEDIFF(MAX(th.change_time), t.create_time) AS open_time FROM ticket AS t JOIN ticket_history AS th ON t.id=th.ticket_id JOIN ticket_history_type AS tht ON th.history_type_id=tht.id WHERE t.ticket_state_id=2 AND tht.id=27 AND th.name LIKE '%closed successful%' AND t.customer_id LIKE '%' GROUP BY t.id; This lead to the following output tn customer_id create_time change_time open_days open_time 2007051510000075 ecohmedia 2007-05-15 12:10:06 2007-05-15 12:18:28 0 00:08:22 2007051510000084 ecohmedia 2007-05-15 12:40:08 2007-05-15 14:46:34 0 02:06:26 2007051510000137 ecohmedia 2007-05-15 16:39:45 2007-05-15 16:44:13 0 00:04:28 2007051610000055 2007-05-16 11:22:09 2007-05-17 10:41:13 1 23:19:04 2007051710000044 2007-05-17 16:23:34 2007-05-17 16:24:08 0 00:00:34 2007051810000079 FATER 2007-05-18 14:48:07 2007-05-18 15:35:58 0 00:47:51 2007051810000122 FATER 2007-05-18 16:03:57 2007-06-19 17:55:30 32 769:51:33 2007061810000013 SANOFI 2007-06-18 09:41:25 2007-06-18 10:07:41 0 00:26:16 This is still not a perfect measurement of time spent to close the ticket, but is good enough for my purposes at the moment. Let's say that a ticket is closed, then reopened, then closed again: then I'm not able to calculate the time the ticket remained closed the first time. To do this, I think I need to use some programming language (like PHP or Perl), and maybe I'll do it later. Then the output format is not perfect too, cause days are integer (and that colud be fine), but time is in hh:mm:ss (should be good to have days hh:mm:ss for longer periods), thus data may need some further manipulation. In the end I wish to thank all the people in this community that give me feedbacks and precious informations, expecially Alexander Scholler Further comments, advices, warnings are also appreciated. Gabriele D'Andrea ----- Original Message ----- From: "Marcus Dennis" <[EMAIL PROTECTED]> To: "User questions and discussions about OTRS.org" <otrs@otrs.org> Sent: Monday, June 18, 2007 7:01 PM Subject: RE: [otrs] Trying to find out the time taken to close a ticket I don't have the database schema in front of me right now, but it sounds like you would probably want to query the ticket history for closing events, ordered by date descending, with a limit of one result? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gabriele D'Andrea Sent: Sunday, June 17, 2007 11:35 PM To: User questions and discussions about OTRS.org Subject: Re: [otrs] Trying to find out the time taken to close a ticket > Why shouldn't is be possible. > Within the ticket-history, just lookup the dates of ticket-creation and > state-change to a close-state-type. That's the gap between creation and > the selected ticket-closure. But how can I retrieve the state_change? I can lookup for ticket "close succesful" state, but there are plenty if operations have been executed after the ticket closure... ----- Original Message ----- From: "Alexander Scholler" <[EMAIL PROTECTED]> To: "User questions and discussions about OTRS.org" <otrs@otrs.org> Sent: Monday, June 18, 2007 8:22 AM Subject: Re: [otrs] Trying to find out the time taken to close a ticket > Hi Gabriele, > > Gabriele D'Andrea schrieb: >> Hi Alex, >> >>> 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. >> >> Yes, I'm looking for this, and it's what i tried to explain: >> I think it's not possible to calculate the time between ticket creation >> and closure, due to the partciular database design. > > Why shouldn't is be possible. > Within the ticket-history, just lookup the dates of ticket-creation and > state-change to a close-state-type. That's the gap between creation and > the selected ticket-closure. >> >> Gabriele > > Bye, Alex > _______________________________________________ > 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/ _______________________________________________ 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/