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

Comments

  • 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....
Please log in to comment

Answers

0

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.

Answered 02/20/2014 by: chucksteel
Red Belt

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