Anton Panetta wrote:
>Hi There
>I'm curious if anyone else has attempted or found a way to achieve this.
>What I am
>Able to make is this (sort for the lack of formatting)
>
> Ticket
>Queue Statuscount
>new 11
>open 5
>rejected 1
>BPA resolved 7
>new 2
>open 3
>rejected 2
>COO Projectsresolved 6
>new 8
>open 3
>resolved 1
>DI stalled 1
>new 6
>Finance resolved 5
>new 12
>IT BI Requests stalled 1
>new 8
>open 8
>IT Helpdesk resolved 42
>new 18
>open 1
>resolved 2
>IT Projects stalled 1
>Total 0 154
>
>What id like to make is closer to this
>
>QueueOpen New Resolved Stalled Rejected Total
>IT 1 51020 18
>DI 2 51030 20
>BI 3 51020 20
>Project 4 51031 23
>Total1020 4010 1
>
>The Idea is to include it in a dashboard.
>I am aware that using the chat tool to make a table is a bit of a side way, I
>could possibly make do if I could make the tables run horizontal.
>Regards
>Anton
You need to make a query that returns 6 values per queue.
Pseudo SQL:
select queue.name as queue, sumOpen, sumNew, sumResolved, sumStalled,
sumRejected, sumTotal
from queue
where queue.disable = 0
join (select count(*) as sumOpen, queue.id
from tickets
where tickets.status = 'open'
and tickers.queueid = queue.id)
join (select count(*) as sumNew, queue.id
from tickets
where tickets.status = 'new'
and tickers.queueid = queue.id)
join (select count(*) as sumResolved, queue.id
from tickets
where tickets.status = 'resolved'
and tickers.queueid = queue.id)
join (select count(*) as sumStalled, queue.id
from tickets
where tickets.status = 'stalled'
and tickers.queueid = queue.id)
join (select count(*) as sumRejected, queue.id
from tickets
where tickets.status = 'rejected'
and tickers.queueid = queue.id)
join (select count(*) as sumTotal, queue.id
from tickets
where tickets.status in
('open','new','resolved','stalled','rejected')
and tickers.queueid = queue.id)
As I don't have easy access to the database, I am not sure I have the syntax or
table names correct. But this should give you an idea of what you need.
Basically, you need to gather the sum of tickets for each type by queue, and
then list them all at once. That needs multiple joins to achieve.
/jeff
The information contained in this e-mail is for the exclusive use of the
intended recipient(s) and may be confidential, proprietary, and/or
legally privileged. Inadvertent disclosure of this message does not
constitute a waiver of any privilege. If you receive this message in
error, please do not directly or indirectly use, print, copy, forward,
or disclose any part of this message. Please also delete this e-mail
and all copies and notify the sender. Thank you.
For alternate languages please go to http://bayerdisclaimer.bayerweb.com
