ITNinja

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


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.

# 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

# 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.

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


Perfect. Thank-You! Saved me a ton of time.