Has anyone compiled such a beast? I am looking to see the number of tickets my employees open by category on a monthly basis
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
[font="times new roman"]You can build most of this via the wizard, and then just subbing in the date routine:
DATE_SUB(NOW(), INTERVAL 31 DAY)

Here is a sample based on the Default K1000 Service Desk Queue to get you going, though if you want this across all the Queues, you will have to build something quite different.

SELECT
S.FULL_NAME AS SUBMITTER_NAME,
HD_CATEGORY.NAME AS CATEGORY
FROM HD_TICKET
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_CATEGORY
ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) )
ORDER BY SUBMITTER_NAME
Answered 01/30/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
My apologies - what I meant to say by user was "Owner"

I am trying to be able to produce a report that basically shows how many tickets an owner closed per category.
Answered 01/30/2012 by: James@Grow
Orange Belt

Please log in to comment
0
I edited your SQL (I had come up with something similar, but your code is much simpler) to get the owner name, and category of ticket. Now if I can just count the number of ticket by type for each owner;..
Answered 01/30/2012 by: James@Grow
Orange Belt

Please log in to comment
0
Have you tried checking the box next to "Show Line Number Column"? :)
Answered 01/30/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Yes, but still not what we are looking for. Basically I'd want to see something like this:

Category Work Report - previous month

James
Application Issue - 5
Hardware Issue - 3
Information Call - 10

Scott
Application Issue - 10
MySQL Questions - 1100
Answered 01/30/2012 by: James@Grow
Orange Belt

Please log in to comment
0
I think we figured it out:

SELECT U2.FULL_NAME, HD_C.NAME AS 'TICKET_TYPE', COUNT(HD_C.NAME) AS 'TICKET_COUNT'
FROM HD_TICKET HD_T
INNER JOIN USER U2 ON U2.ID = HD_T.OWNER_ID
INNER JOIN HD_CATEGORY HD_C ON HD_C.ID = HD_T.HD_CATEGORY_ID
WHERE HD_T.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
AND HD_T.OWNER_ID <> 0
GROUP BY FULL_NAME, TICKET_TYPE
ORDER BY FULL_NAME, TICKET_TYPE ASC;
Answered 01/30/2012 by: James@Grow
Orange Belt

Please log in to comment
Answer this question or Comment on this question for clarity