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