[SOLVED] Query track time per state

[gecelann]

Hi

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
	

I used this topic as reference.
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

Selection_049.png

[benjamin1]

I remember having same issue, adding a second order by fixed it for me e.g.

SELECT ts.name state,
th.name fromto
,
th.create_time
createtime,
th.ticket_id
ticket_id,
th.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
order by th.id, th2.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
order by th.id, th2.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)
AND ts.id IN (6,9,11,14,25)
ORDER BY id

[gecelann]

I remember having same issue, adding a second order by fixed it for me e.g.

SELECT ts.name state,
th.name fromto
,
th.create_time
createtime,
th.ticket_id
ticket_id,
th.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
order by th.id, th2.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
order by th.id, th2.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)
AND ts.id IN (6,9,11,14,25)
ORDER BY id

It works!! Thank you so much, Benjamin!