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: [email protected]
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/
_______________________________________________
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/
