Hello,
What I want to accomplish is a report that will show me a count for each owner based on the number of "Unassigned" tickets that they pick up in a given time period.  I already have a total count by owner.  The problem is that sometimes the helpdesk will create their own tickets based on phone calls and I need to weed those out from the report.  I need to see who is picking up helpdesk tickets that are sent in via email and list the owner initially as "Unassigned".  I would assume that I would need to use HD_TICKET_CHANGE.OWNER_NAME variable.  I've spent a little over a day on this and getting nowhere.  Any help would be appreciated.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
I would start with the HD_TICKET_CHANGE_FIELD table and look for changes where the changed field is OWNER_ID and the previous value was 0 (for unassigned). This query pulls in the username and timestamp for the change:

SELECT HD_TICKET_CHANGE_FIELD.*, USER.USER_NAME, HD_TICKET_CHANGE.TIMESTAMP
FROM ORG1.HD_TICKET_CHANGE_FIELD
JOIN USER on USER.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID
WHERE FIELD_CHANGED = "OWNER_ID"
AND BEFORE_VALUE = 0;

Changing that to get a count and grouping by the after value (the new owner) gets you this:
SELECT COUNT(HD_TICKET_CHANGE_FIELD.ID), USER.USER_NAME
FROM ORG1.HD_TICKET_CHANGE_FIELD
JOIN USER on USER.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID
WHERE FIELD_CHANGED = "OWNER_ID"
AND BEFORE_VALUE = 0
AND TIMESTAMP > NOW() - INTERVAL 30 DAY
GROUP BY AFTER_VALUE

That query is also limited to the previous 30 days.
Answered 09/18/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share