I am needing to monitor the activity on the queue.  Since I do not see an option to display the last comment date, has anyone created a report to generate it?
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Here's a version that specifies a queue and only reports on open tickets. I'm also using a join statement that gets the most recent change with a comment.
SELECT T.ID, T.TITLE, C.COMMENT, C.TIMESTAMP, U.USER_NAME 
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID and C.ID = (select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.COMMENT != '')
JOIN USER U on U.ID = C.USER_ID 
WHERE T.HD_QUEUE_ID = 2
and T.TIME_CLOSED = "0000-00-00 00:00:00"
Be sure to change the T.HD_QUEUE_ID = 2 line to match the queue ID for your queue. 
Answered 06/11/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

0
Did you mean something like this?

select q.NAME, t.TITLE, t.SUMMARY, t.CREATED, tc.TIMESTAMP, u.FULL_NAME, tc.COMMENT
  from HD_TICKET as t, HD_TICKET_CHANGE as tc, HD_QUEUE as q, USER as u
 where t.ID = tc.HD_TICKET_ID 
   and q.ID = t.HD_QUEUE_ID
   and u.ID = tc.USER_ID
   and (tc.COMMENT is not null and tc.COMMENT != '')
order by tc.TIMESTAMP desc
Answered 06/11/2015 by: aragorn.2003
Red Belt

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