Custom report showing count by owner for picked up "unassigned" tickets
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.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
10 years ago
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.
