/build/static/layout/Breadcrumb_cap_w.png

Report Query Last Comment

Hi,

I am try to write a repory query to return the last comment (aka most recent comment) on a ticket.  I thought all I had to do was use MAX() and GROUP BY but it does not appear to be working.  Any ideas?  Thanks in advance for the help!

SELECT T.ID, TC.TIMESTAMP, TC.COMMENT
FROM HD_TICKET T
LEFT OUTER JOIN (SELECT DISTINCT MAX(TC.TIMESTAMP) AS TIMESTAMP, TC.HD_TICKET_ID, TC.COMMENT
                                FROM HD_TICKET_CHANGE TC
                                GROUP BY TC.HD_TICKET_ID, TC.COMMENT) TC ON T.ID = TC.HD_TICKET_ID
WHERE T.ID = 1234

 

Query Results:

 T.IDTC.TIMESTAMP TC.COMMENT 

1234

1234

2014-11-28 10:37:50

2014-12-01 10:50:25

I checked this morning.

Sent resolution.

Expected Query Results:

 T.IDTC.TIMESTAMP TC.COMMENT 

1234

2014-12-01 10:50:25

Sent resolution.


1 Comment   [ + ] Show comment
  • I do not have SQL access today to my lab but I can tell you that using MAX is a good idea but the things are a little bit more complex than this to get the last comment...
    You need to use the MAX to find the most recent comment date and then use it in a WHERE condition.
    You can start to have a look to this http://www.techonthenet.com/sql/max.php
    especially the first FAQ...you may find the answer inspiring :-)
    Kind regards,
    Marco = StockTrader - StockTrader 9 years ago

Answers (2)

Answer Summary:
Posted by: KevinG 9 years ago
Red Belt
2
SELECT T.ID as 'Ticket ID', MAX(TC.TIMESTAMP) as 'Last Comment Date', TC.COMMENT as 'Ticket Comment'
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE TC ON TC.HD_TICKET_ID = T.ID
where T.ID = 7

Change "where T.ID = 7" to match your ticket #

If your goal is to write a report on all tickets with their last comment timestamp.

SELECT T.ID as 'Ticket ID', MAX(TC.TIMESTAMP) as 'Last Comment Date', TC.COMMENT as 'Ticket Comment'
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE TC ON TC.HD_TICKET_ID = T.ID
group by T.ID
Posted by: kaneda0149 9 years ago
Orange Senior Belt
0

Hi KevinG, thank you getting me in the right direction.  I tried both queries (and your right I do want all), it returned the latest (i.e. last) timestamp but the strange part about it was it returned the actual first comment instead of the last comment on the ticket change event which was really weird.  So went another route and was able to return the actual last comment.  I re-wrote the following syntax.

SELECT DISTINCT TC.HD_TICKET_ID, TC2.LASTCOMMENTDATE, TC.COMMENT
FROM ORG1.HD_TICKET_CHANGE TC
LEFT OUTER JOIN (SELECT T.ID, MAX(TC.TIMESTAMP) as 'LASTCOMMENTDATE'
                FROM ORG1.HD_TICKET T
                JOIN ORG1.HD_TICKET_CHANGE TC ON TC.HD_TICKET_ID = T.ID
                group by T.ID) AS TC2 ON TC.HD_TICKET_ID = TC2.ID AND TC.TIMESTAMP = TC2.LASTCOMMENTDATE
WHERE TC.HD_TICKET_ID = 1234
AND TC2.LASTCOMMENTDATE IS NOT NULL

 

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