Quantity of tickets by submitter
I would like to get a simple report that shows the total number of tickets by submitter. Something like the output from the SQL statement in http://itninja.com/question/number-of-helpdesk-tickets-closed-by-owner-in-the-last-30days - except looking at submitter instead of owner.
Thanks for any input!
Dennis
Thanks for any input!
Dennis
0 Comments
[ + ] Show comments
Answers (4)
Please log in to answer
Posted by:
airwolf
13 years ago
Simply change instances of Owner to Submitter.
SELECT IFNULL(SUB.FULL_NAME,'Unassigned') AS 'Submitter', COUNT(T.ID) AS '# of Tickets' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER SUB ON (T.SUBMITTER_ID = SUB.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 30)
GROUP BY SUB.FULL_NAME
Posted by:
ddevore
13 years ago
Nice - Thanks!! Just in case anyone else wants to organize it by ticket quantity add "order by COUNT(T.ID) DESC" as another line at the bottom.
Posted by:
hchavarria
13 years ago
This has come in very handy, any way to limit the results to a date range and to a specific queue?
Posted by:
dchristian
13 years ago
You'll need to modify the where statement to filter either ticket created or closed (depending what you care about) and the HD_QUEUE filed.
Give the SQL a shop we'll help if you get stuck.
Give the SQL a shop we'll help if you get stuck.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.