/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 10 years ago
Red Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ