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

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
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
Answered 06/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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.
Answered 06/03/2010 by: snocera13
Senior Yellow Belt

Please log in to comment
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
Answered 06/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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.
Answered 06/03/2010 by: RichB
Third Degree Green Belt

Please log in to comment
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
Answered 06/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Perfect. Thank-You! Saved me a ton of time.
Answered 06/03/2010 by: snocera13
Senior Yellow Belt

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