Code: Select all
SELECT
'Queue',
'Ticket',
'Total Messages',
'Agent Messages',
'Client Messages',
'Current State',
'Agent',
'Last Change date'
UNION SELECT
q.name qn,
t.tn ttn,
count(a.id) nbm,
count(CASE WHEN a.article_sender_type_id = 1 AND a.article_type_id in (1,2,3,4) then 1 ELSE NULL END) nba,
count(CASE WHEN a.article_sender_type_id = 3 AND a.article_type_id in (1,2,3,4) then 1 ELSE NULL END) nbc,
ts.name stf,
u.login usrn,
t.change_time tct
FROM
otrs.ticket t
left join queue q
on t.queue_id = q.id
left join article a
on a.ticket_id = t.id
left join users u
on t.user_id = u.id
left join ticket_state ts
on t.ticket_state_id = ts.id
WHERE
q.id not like '3' AND
t.change_time BETWEEN '2014-01-01 09:00:00' and '2014-01-21 17:00:00'
GROUP BY t.tn
Queue id '3' is our Junk Queue, modify for your setup.
The "total messages" column includes automated replies, which are not counted on the Agent and Client messages columns.