SQL Box query: Number of messages per ticket

[nonobots]

We wanted to track how many messages where issued per tickets. In the hope of tracking what areas/queues are harder to support and makes more back and forth between the client and our agents. I came up with this SQL we are using in the SQLBox:

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

Some notes:

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.