MySQL query to select escalated tickets

[timurtamerlan]

Dead OTRS gurus!

Please, help me to build mysql query to SELECT tickets that have been escalated. I did not find any flag like "isEscalated" in the "tickets" table.

I'm trying to build very custom weekly report to analyze the performance of the customer support service, that will show such figures as:

- Total number of tickets by service
- Number of escalates tickets by service
- Number of successfully closed tickets by service
- Number of tickets that are still open by service

If this is a bicycle and it has been invented already, I would be very grateful if you point at it -)

[crythias]

Do we need a seance to call up the zombie OTRS gurus?

Look in ticket for escalation_time escalation_update_time escalation_response_time escalation_solution_time

anything nonzero is a "hit"

These are int(11) and are therefore epoch time seconds.
example:

Code: Select all

SELECT FROM_UNIXTIME(escalation_update_time) FROM ticket WHERE escalation_time BETWEEN UNIX_TIMESTAMP('20110601') AND UNIX_TIMESTAMP('20110630');

http://dev.mysql.com/doc/refman/4.1/en/ ... -timestamp

[kruegerM]

just checking ...

for escalation_solution_time, is it

SELECT FROM_UNIXTIME(escalation_solution_time) FROM ticket WHERE escalation_time BETWEEN UNIX_TIMESTAMP('20110601') AND UNIX_TIMESTAMP('20110630');

or

SELECT FROM_UNIXTIME(escalation_solution_time) FROM ticket WHERE escalation_SOLUTION_time BETWEEN UNIX_TIMESTAMP('20110601') AND UNIX_TIMESTAMP('20110630');

?

[crythias]

probably the latter.