Service Desk Report with only latest comment
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.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
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.
Comments:
-
Thanks! This gave me the results I needed. - pregiec 8 years ago
