Hello,

I am running the following Service Desk report:

SELECT HD_TICKET.TITLE, HD_TICKET.CREATED, HD_TICKET.DUE_DATE, O.FULL_NAME AS OWNER_NAME, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED  FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND (((TIMESTAMP(HD_TICKET.MODIFIED) <= NOW() AND TIMESTAMP(HD_TICKET.MODIFIED) > DATE_SUB(NOW(),INTERVAL 30 DAY))) AND (HD_PRIORITY.NAME = 'H5 (Project)'))  GROUP BY HD_TICKET.ID ORDER BY OWNER_NAME

How can I adjust this so that the outcome only displays the most recent comment and not all the comments. If this cannot be done, is there a way to separate all the comments with a time stamp? Any help would be appreciated.


Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
I do this by adjusting the join to the HD_TICKET_CHANGE table. Your current join is this:
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)

Instead you can use this:
LEFT JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = (select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID and HD_TICKET_CHANGE.COMMENT != '')

This will make the join only match the most recent change that has a comment. Once you do this you can also change this:
GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED
to just this:
HD_TICKET_CHANGE.COMMENT
since you no longer need to group multiple comments.

Answered 05/26/2016 by: chucksteel
Red Belt

  • Thanks! This gave me the results I needed.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share