Hi, I'm trying to get stats broken down by agent at the end of each day, I'm looking for: Emails sent Replies received Number of tickets set to pending Number of tickets set to closed and just for completeness, the number of state changes to any ticket by the agent.
Emails sent: select th.change_by as user_id, count(change_by) as emails_sent from ticket_history th where th.change_time < '2010-09-24' and th.change_time >= '2010-09-23' and th.history_type_id in (select id from ticket_history_type where name = 'SendAnswer' ) group by change_by Would this be correct? Replies recived: select t.change_by as user_id, count(t.change_by) as replies_recived from article art left join ticket t on (art.ticket_id = t.id) where art.article_type_id in (select artt.id from article_type artt where artt.name = 'email-external') and t.queue_id in (select q.id from queue q) and t.ticket_state_id not in (select ts.id from ticket_state ts where ts.name = 'new') and art.change_time < '2010-09-24' and art.change_time >= '2010-09-23' and art.article_sender_type_id in (select ast.id from article_sender_type ast where ast.name = 'customer') group by t.change_by Does this look correct? I think this shows against the ticket's current owner but I could be wrong. Number of tickets set to pending: select change_by as user_id, count(change_by) as pending_set from ticket t where t.queue_id in (select q.id from queue q) and t.ticket_state_id in (select ts.id from ticket_state ts where ts.name like 'pending auto close%') and t.change_time < '2010-09-24' and t.change_time >= '2010-09-23' group by change_by The numbers here seem a lot lower than I would expect. Number of tickets set to closed: select change_by as user_id, count(change_by) as closed_set from ticket t where t.queue_id in (select q.id from queue q) and t.ticket_state_id in (select ts.id from ticket_state ts where ts.name like 'closed%') and t.change_time < '2010-09-24' and t.change_time >= '2010-09-23' group by change_by Like the pending one, the numbers seem low. What am I doing wrong? Can someone help? Thanks, Josh. --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
