[otrs] calculating answer time

[Gabriele D'Andrea]

Hi Tabitha,
I believe it's possible to do what you want, but it's a bit more complex than a simple query.

You should do something like this:
browse the ticket_history table in order to find the events you're looking for based on the history_type_id (which refers to a series of predefined events on the ticket_history_type table), then you can calculate time differences between the events.

I suggest to use some kind of programming language, like Perl or PHP.

I'm planning to do something similar to calculate the opening time, but it's not a priority at the moment, so I don't know if and when I'll do this.

Gabriele

  ----- Original Message ----- 
  From: Tabitha Stang 
  To: otrs at otrs.org 
  Sent: Friday, December 21, 2007 4:06 PM
  Subject: [otrs] calculating answer time


  Hi,

   

  We'd like to be able to calculate the time it takes for us to answer tickets.  

   

  I found the following code in the logs (slightly modified):

  SELECT ticket.tn, timediff(ticket_history.create_time, ticket.create_time) AS 'diff' from ticket LEFT JOIN ticket_history ON ticket.id=ticket_history.ticket_id

  WHERE ticket.create_time > '2007-11-01 00:00:00'

  AND ticket_history.history_type_id = 15 

  AND ticket_history.name = '%%close'

  AND ticket_history.create_time > '2007-11-01 00:00:00' 

  ORDER BY ticket_history.create_time

   

  Unfortunately this has one small problem for us. 

   

  For example: If a customer write to us on Nov 1 and we answer immediately, then he writes again on November 5 and we answer immediately, the "time difference" calculated is 4 days. This messes up our statistics - since really we answered immediately each time!

   

  Could anyone tell me how I can either:

  1) calculate the difference between one contact and its corresponding answer OR

  2) find out which customers have written more than once on the same ticket, such that I can exclude them in my query.

  3) Any other workarounds?

   

  Thanks very much and happy holidays!

   

  Tabitha

   



------------------------------------------------------------------------------


  _______________________________________________
  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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.otrs.org/pipermail/otrs/attachments/20080102/9771411f/attachment.html