[SOLVED] Query No Moved Ticket's - Little Help!!

[Soldos81]

HI,
I am trying, without great success, to make a QUERY, where I want to get only the tikcet's that have not been moved to any QUEUE.
The tickets are opened, by default in the Service Desk queue, and if they are resolved immediately (Fist Time Fix), they are not forwarded to any technical queues.
I thought I had successfully done that with the query below, however, some tickets are missing, and I can not understand why. all that appear in the list returned by the query are correct.
I am on my 3º week dedicated only to this, my knowledge of MYSQL is close to 0, but I have managed, with google help, to do all of them. Except this one ...
Can anyone see if I'm missing something?

Code: Select all

WITH JMActivities AS (
SELECT th.ticket_id ID, 
 
t.tn Ticket, 
t.title Title, 
ts.name State_Detail,
( SELECT tp.name FROM otrsprd.ticket_priority tp WHERE t.ticket_priority_id=tp.id) Priority, 
t.customer_id Customer, 
cu.login 'User/cod_Loja', 
t.create_time Reported_Date, 
(SELECT a.create_time FROM otrsprd.article a WHERE a.ticket_id=t.id LIMIT 1) Resolved_Date,
(Select q.name FROM otrsprd.queue q WHERE q.id=th.queue_id) Queue,
s.name as Service
FROM otrsprd.ticket_history th 
INNER JOIN otrsprd.ticket t ON t.id=th.ticket_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
WHERE 
 th.ticket_id   IN (SELECT ticket_id FROM otrsprd.ticket_history WHERE history_type_id!=16)
and  (ts.type_id = 5 OR ts.type_id = 3)
-- and ts.id IN (7)
and (th.create_time between  '2018-09-01 00:00:00' and '2018-12-21 23:59:59') -- and t.tn like '580027767'
GROUP BY th.ticket_id)
SELECT * FROM JMActivities

[Soldos81]

Hey...
I managed to get the info I need...

just for reference.. if anyone need something similar..

Code: Select all

select distinct(otrsprd.th.ticket_id),t.tn as 'Nº Ticket', q.name as Fila, t.title as Assunto, t.create_time as 'Data_Criação', th.create_time  'Passado a resolved em', s.name as Serviço
from ticket_history th 
INNER JOIN otrsprd.ticket t ON t.id=th.ticket_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
where th.ticket_id not in ( select distinct(otrsprd.th.ticket_id) 
							from ticket_history th
							where th.history_type_id like 16) and th.history_type_id =  '27' and  (t.create_time between  '2018-09-01 00:00:00' and '2018-12-23 23:59:59')
                            Group by th.ticket_id