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