I'm sorry I posted a version of the query which is customized for our
environment, I repost a the genarl query
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;
----- Original Message -----
From: Gabriele D'Andrea
To: User questions and discussions about OTRS.org
Sent: Tuesday, October 23, 2007 2:16 PM
Subject: Re: [otrs] Stats reports ?
I wrote a SQL query to do that.
This one queries the database for all closed succesful tickets, calculating
the time between creation and closing.
SELECT tn, customer_id, create_time, freetime1 AS real_close_time,
DATEDIFF(freetime1, create_time) AS open_days, TIMEDIFF(freetime1, create_time)
AS open_time
FROM ticket
WHERE ticket_state_id=2 AND '2007-01-01'<create_time AND
create_time<'2007-12-31' AND customer_id LIKE '%';
Now I'm writing something more complete in PHP
Bye
Gabriele
----- Original Message -----
From: Agim Cami
To: [email protected]
Sent: Monday, October 22, 2007 3:08 PM
Subject: [otrs] Stats reports ?
I would like to check the performance of the sevice team.
I tried to create reports, for exemple, based on difference of ticket's
creation time and ticket's close time.
This is not possible.
Have somebody any exsperience on this issue?
Agim
----------------------------------------------------------------------------
_______________________________________________
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/
