List All QUEUES THAT a Ticket HAD.... Yes is possible

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

Forum rules
Dont create your support topics here! No new topics with questions allowed!
Post Reply
Soldos81
Znuny newbie
Posts: 9
Joined: 02 Oct 2017, 12:36
Znuny Version: 5/4/3.3
Real Name: Bruno Graça
Company: Softinsa

List All QUEUES THAT a Ticket HAD.... Yes is possible

Post by Soldos81 »

Hi,
After the request of a manager, we arrived at a query that gave us the desired output.
You have been asked to list all the queues in which each ticket has passed.
the result was:
Basically we get the first QUEUE (no MOVE Action) on ticket creation, and the rest, identify each queue with the time on each one.

Code: Select all

SELECT distinct(ticket_history .id), ticket_history.ticket_id as ID, t.tn as 'Numero Ticket', t.create_time as 'Data criação Ticket',ticket_history.name as 'Fila destino_Origem',ts.name as ESTADO,
ticket_history.create_time as 'Data de entrada na fila' , if (cl.create_time IS NULL,"",cl.create_time) as 'CLOSE TIME', if (rs.create_time IS NULL,"",rs.create_time) as 'Resolved TIME'
FROM ticket_history
INNER JOIN ticket t ON ticket_id = t.id
INNER JOIN otrsprd.ticket_type tt ON t.type_id=tt.id
INNER JOIN otrsprd.ticket_state ts ON t.ticket_state_id=ts.id
INNER JOIN otrsprd.ticket_state_type tst ON ts.type_id=tst.id
INNER JOIN otrsprd.customer_user cu ON t.customer_user_id=cu.login
INNER JOIN otrsprd.queue q ON t.queue_id=q.id
INNER JOIN otrsprd.dynamic_field_value df ON t.id=df.object_id
INNER JOIN otrsprd.service s on t.service_id=s.id
LEFT JOIN (
SELECT id, ticket_id, state_id, max(create_time) create_time
FROM ticket_history WHERE history_type_id=27 AND state_id in (2,3,10)
GROUP BY ticket_id
) cl ON cl.ticket_id = t.id
LEFT JOIN (
SELECT id, ticket_id, state_id, max(create_time) create_time
FROM ticket_history WHERE history_type_id=27 AND state_id in (7)
GROUP BY ticket_id
) rs ON rs.ticket_id = t.id
where history_type_id like 1 and (t.create_time between DATE_FORMAT(NOW() ,'%Y-%m-01') and curdate())
union ( 
select
distinct(ticket_history .id), ticket_history.ticket_id as ID, t.tn as 'Numero Ticket', t.create_time as 'Data criação Ticket',ticket_history.name as 'Fila destino_Origem',ts.name as ESTADO,
ticket_history.create_time as 'Data de entrada na fila' , if (cl.create_time IS NULL,"",cl.create_time) as 'CLOSE TIME', if (rs.create_time IS NULL,"",rs.create_time) as 'Resolved TIME'
FROM ticket_history
INNER JOIN ticket t ON ticket_id = t.id
INNER JOIN otrsprd.ticket_type tt ON t.type_id=tt.id
INNER JOIN otrsprd.ticket_state ts ON t.ticket_state_id=ts.id
INNER JOIN otrsprd.ticket_state_type tst ON ts.type_id=tst.id
INNER JOIN otrsprd.customer_user cu ON t.customer_user_id=cu.login
INNER JOIN otrsprd.queue q ON t.queue_id=q.id
INNER JOIN otrsprd.dynamic_field_value df ON t.id=df.object_id
INNER JOIN otrsprd.service s on t.service_id=s.id
LEFT JOIN (
SELECT id, ticket_id, state_id, max(create_time) create_time
FROM ticket_history WHERE history_type_id=27 AND state_id in (2,3,10)
GROUP BY ticket_id
) cl ON cl.ticket_id = t.id
LEFT JOIN (
SELECT id, ticket_id, state_id, max(create_time) create_time
FROM ticket_history WHERE history_type_id=27 AND state_id in (7)
GROUP BY ticket_id
) rs ON rs.ticket_id = t.id
where ts.id !=5 and history_type_id=16 and (t.create_time between DATE_FORMAT(NOW() ,'%Y-%m-01') and curdate())
)


 

 
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
Post Reply