/build/static/layout/Breadcrumb_cap_w.png

Number of helpdesk tickets closed by owner in the last 30days

I need to create a simple report on my Kbox that show the number of helpdesk tickets closed by each member of our helpdesk team during the last 30days. Can someone assist me with SQL code for this type of report?

0 Comments   [ + ] Show comments

Answers (6)

Posted by: airwolf 11 years ago
Red Belt
0
The SQL code would be similar to the report I wrote HERE.

Make sure you set the "Break on Field" in the report to the Owner field (just type "Owner" without quotes in the Break on Field box).


SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', T.TIME_OPENED AS 'Time Opened', T.TIME_CLOSED AS 'Time Closed', IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', U.FULL_NAME AS 'Submitter', S.NAME AS 'Status', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 30)
ORDER BY O.FULL_NAME, S.NAME, P.NAME, T.TIME_OPENED
Posted by: snocera13 11 years ago
Senior Yellow Belt
0
Thanks for the reply. All I need though is # of calls and Owner name listed? Would you be able to modify your code to show only that? I've tried, but i am no sql programer.
Posted by: airwolf 11 years ago
Red Belt
0
# of calls is a custom field, so I can only sort of write the code for you. To find the custom field number, go into the field customization page for your Queue and find the Custom field number. Field numbers are listed 1-15, but the values in the database are for 0-14. For instance, custom field 1's value field in the database is CUSTOM_FIELD_VALUE0.

You need to find out the field number for your "# of calls" field. Then, modify the following code accordingly:

SELECT T.ID AS 'Ticket #', T.CUSTOM_FIELD_VALUE0 AS '# of Calls', IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 30)
ORDER BY O.FULL_NAME
Posted by: RichB 11 years ago
Second Degree Brown Belt
0
# of calls is a custom field


I think they meant tickets so a summary of tickets per Owner is what he's looking for and I'd be interested in that too.
Posted by: airwolf 11 years ago
Red Belt
0
ORIGINAL: RichB


# of calls is a custom field


I think they meant tickets so a summary of tickets per Owner is what he's looking for and I'd be interested in that too.


In that case, this would work. And you don't need to use the "Break on Field" for this one.


SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 30)
GROUP BY O.FULL_NAME
Posted by: snocera13 11 years ago
Senior Yellow Belt
0
Perfect. Thank-You! Saved me a ton of time.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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