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.

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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
Please log in to comment

Community Chosen Answer

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
Answered 12/13/2014 by: KevinG
Purple Belt

Please log in to comment

Answers

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

 

Answered 12/15/2014 by: kaneda0149
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity