/build/static/layout/Breadcrumb_cap_w.png

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

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 7 years ago

Don't be a Stranger!

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

Sign up! or login

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