good day!
I would like to know how to optimize this sql query, I need to know how to get the optimized query for the order by of ticket_history id to have the right sorting of the create_time of ticket_history
Code: Select all
SELECT th.id,
ts.name state,
th.name fromto,
th.create_time createtime,
t.tn ticket_number,
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
ORDER BY th2.id
LIMIT 1), Now())
custom_date,
TIMEDIFF( 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
ORDER BY th2.id
LIMIT 1), Now()),th.create_time) diff
FROM `ticket_history` th
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
LEFT JOIN ticket t
ON t.id=th.ticket_id
WHERE th.history_type_id IN ( '1', '27' )
ORDER BY th.ticket_id, th.create_time ASC
viewtopic.php?t=14242
I used this query instead of this query below, because this query does not give me the exact end date which I need to have in order to calculate the time per state. It gives me time from new to resolved state. Please see the screenshot
Code: Select all
SELECT th.id,
ts.name state,
th.name fromto,
th.create_time createtime,
t.tn ticket_number,
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,
TIMEDIFF( 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()),th.create_time) diff
FROM `ticket_history` th
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
LEFT JOIN ticket t
ON t.id=th.ticket_id
WHERE th.history_type_id IN ( '1', '27' )
ORDER BY th.ticket_id, th.create_time ASC