How to include last "add comment" or "add work" comments into a Helpdesk Report
We have a report that lists all the open tickets, specifying category, owner, etc. I was wondering how I would be able to include the last work note into that report. I don't need all comments, just the most recent comment that was entered using the "add comment" or "add work" buttons.
1 Comment
[ + ] Show comment
-
When I use the below, I get an error - Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID and LAST' at line 18. Not sure what I have wrong.... - jpanich 10 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
10 years ago
To get the most recent comment you need to join to the HD_TICKET_CHANGE table:
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
You could do the same thing to join to the HD_WORK table:
JOIN HD_WORK LASTWORK_CHANGE ON LASTWORK_CHANGE.HD_TICKET_ID = HD_TICKET.ID and LASTWORK_CHANGE.ID=(select MAX(ID) from HD_WORK where HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
To select the comments or note in the report you would need to add LAST_CHANGE.COMMENT or LASTWORK_CHANGE.NOTE to the select statement in your query. If you want the latest of either of these you would have to build a case or if statement around them. I would do that based on which has the more recent timestamp.
