/build/static/layout/Breadcrumb_cap_w.png

ITNinja Question


Tickets opened by user by Category

01/27/2012 1958 views
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


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

All 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

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

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

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

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

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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ