Reports

[ronaldjerardmedina]

I want to make a query that has an output like this

OWNER STATE FROMTO CREATETIME TICKET_ID CUSTOM_DATE DIFF
root@localhost new New Ticket [2015071510123456] created. 2016-07-28 15:08:50 1 2016-07-29 10:47:39 70729
ronald.medina closed successful %%new%%Closed Successful%% 2016-07-29 10:47:39 1 2016-10-20 15:43:07 7188928
ronald.medina closed successful %%open%%Closed Successful%% 2016-07-29 10:57:29 2 2016-10-20 15:43:07 7188338
root@localhost Open %%new%%open%% 2016-07-28 15:19:32 2 2016-07-29 10:57:29 70677
root@localhost new %%2016072816000019%%Postmaster%%3 normal%%new%%2 2016-07-28 15:18:52 2 2016-07-28 15:19:32 40
root@localhost Open %%new%%open%% 2016-07-28 16:44:52 3 2016-07-29 10:58:08 65596
root@localhost new %%2016072816000028%%Postmaster%%3 normal%%new%%3 2016-07-28 16:37:11 3 2016-07-28 16:44:52 461
ronald.medina closed successful %%open%%Closed Successful%% 2016-07-29 10:58:08 3 2016-10-20 15:43:07 7188299

but, i want to change the ticket_id to ticket number.
can someone help me?

I used this query for the above output.
PS: I found this query here on forums also.

SELECT u.login owner,
ts.name state,
th.name fromto
,
th.create_time
createtime,
th.ticket_id
ticket_id,
Coalesce((SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ( '1', '27' )
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
LIMIT 1), Now())
custom_date,
Timestampdiff(second, th.create_time, Coalesce(
(SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN (
'1', '27' )
AND th2.ticket_id =
th.ticket_id
AND th2.id > th.id
LIMIT 1), Now())) diff
FROM `ticket_history` th
LEFT JOIN users u
ON u.id = th.owner_id
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
WHERE th.history_type_id IN ( '1', '27' )
ORDER BY ticket_id