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 Status count
new 11
open 5
rejected 1
BPA resolved 7
new 2
open 3
rejected 2
COO Projects resolved 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
Queue Open New Resolved Stalled Rejected Total
IT 1 5 10 2 0 18
DI 2 5 10 3 0 20
BI 3 5 10 2 0 20
Project 4 5 10 3 1 23
Total 10 20 40 10 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