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.ID | TC.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.ID | TC.TIMESTAMP | TC.COMMENT |
1234 | 2014-12-01 10:50:25 | Sent resolution. |
Answers (2)
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
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
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