K1000 Service Desk Report to show tickets with no updates by owner

Question

I am trying to create a report that will show tickets that have no updates on them by the tech that owns the ticket.

Accepted Answer

Schema for reference here: http://blog.foreignkid.net/wp-content/uploads/2011/04/schema_bootkamp.jpg, but this should start you out:

SELECT T.ID AS TICKET_ID, T.TITLE, T.RESOLUTION, TC.COMMENT

FROM HD_TICKET T

LEFT JOIN HD_TICKET_CHANGE TC ON (TC.HD_TICKET_ID = T.ID)

WHERE ((T.RESOLUTION = '') OR (TC.COMMENT = ''))

GROUP BY T.ID

ORDER BY T.ID

Answer

Best place to start would be probably the ticket change table, as you can then link it to HD_TICKET to pull the owner ID.

Scripting