/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Service Desk Report with only latest comment

05/25/2016 911 views
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:
0 Comments   [ + ] Show comments

Comments


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.

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