Hi Alex
---
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).
---
Yes, I know, but since I filtered only closed tickets (WHERE
t.ticket_state_id=2), it's not necessary cause the last state_change is surely
the ticket closing.
However you are right, a more accurate clause would be the following
... AND th.name LIKE '\%\%%\%\%closed successful%'
---
> 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" <[EMAIL PROTECTED]>
To: "User questions and discussions about OTRS.org" <[email protected]>
Sent: Wednesday, June 20, 2007 8:31 AM
Subject: Re: [otrs] Trying to find out the time taken to close a ticket
Hi Gabriele,
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;
I didn't check you query extensive, but I think it's not complete correct.
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 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.
You can write a Static-stats-OTRS-module for this.
> 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.
No problem.
>
> Gabriele D'Andrea
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/
