sorry for the multi-post ... t.ticket_lock_id (underscore, not dash/minus)
On Thu, Apr 17, 2014 at 9:18 AM, Gerald Young <cryth...@gmail.com> wrote: > I suppose you might want to count states per queue, right? > > sum(case when q.id = 2 and t.state_id=1 then 1 else 0 end) as RawNew > sum(case when q.id = 2 and t.state_id=4 then 1 else 0 end) as RawOpen > > You can do something similar with ticket_state_type instead for types > instead of explicit states. > You may also throw in locked/unlocked with t.ticket-lock_id (Raw Locked, > Raw Unlocked) > > Certainly, the possibilities abound at this point, but I think you get the > idea. > > > On Thu, Apr 17, 2014 at 8:58 AM, Gerald Young <cryth...@gmail.com> wrote: > >> > how many tickets per agent, per queue >> >> SELECT u.login user, sum(case when q.id = 2 then 1 else 0 end) as Raw, >> sum(case when q.id = 3 then 1 else 0 end) as Junk, sum(case when q.id = >> 4 then 1 else 0 end) as Misc FROM `ticket` t left join users u on u.id = >> t.user_id left join queue q on q.id = t.queue_id group by u.id >> >> This will be a bit tedious to assemble, because it relies on specifying >> your individual list of queues: >> >> This can help you generate: >> SELECT<http://192.168.1.90/pmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fselect.html&server=0&token=fca7ecd142b9d15a91e6e67088f6434b> >> CONCAT("SUM(CASE WHEN q.id=", id, " then 1 else 0 end) AS ", q.name, ", >> ") Query FROM `queue` q WHERE 1 >> >> Just remember the last entry before "FROM" must not have a comma. >> >> >> On Tue, Apr 15, 2014 at 6:40 PM, André Cavalcante >> <treba.an...@gmail.com>wrote: >> >>> Hi there, >>> >>> Once I don't have a graphic dashboard plugin, I'd like to build some >>> queries on otrs db (mysql) to show the results in Pentaho. >>> >>> Problem: I don't know in which table data are. >>> >>> Desired result: A graphic dahsboard that shows: >>> - how many tickets are in each queue >>> - how many tickets are locked and unlocked in each queue >>> - how many tickets have escalated per queue >>> - how many tickets per agent, per queue >>> - survey results >>> and other that my imagination can think about. >>> >>> Does anyone know where I can get these information? >>> >>> Thanks >>> >>> >>> >>> -- >>> >>> *André Luiz C. e Cavalcante, PMP, PRINCE2* >>> ITS Manager >>> >>> --------------------------------------------------------------------- >>> 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 >>> >> >> >
--------------------------------------------------------------------- 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
