State-changes are recorded in ticket_history with writing in
ticket_history.names '%%prev_state%new_state%%'.
You just query for '%closed successful%', so you also receive all
tickets with prev_state = 'closed sucessful'. Correct would be
'%\%\%closed sucessful\%' (if '%' is masked within SQL as '\%' - I don't
know).

> 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.

You can write a Static-stats-OTRS-module for this.

---

I know, but I don't know anything about Perl!!! :P

Bye

Gabriele

----- Original Message -----

From: "Alexander Scholler" <alexander.scholler@augsburg.de>

To: "User questions and discussions about OTRS.org" <otrs@otrs.org>

Sent: Wednesday, June 20, 2007 8:31 AM

Subject: Re: [otrs] Trying to find out the time taken to close a ticket

Gabriele D'Andrea schrieb:
> 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.