SQL help please
I am not a MySQL ninja, looking for a little help. We have a report listed below which creates a report of information on misassigned tickets. Does anyone have any idea how to add the Help Desk Agent who originally created the ticket and the Categories originally assigned? SQL for original report listed below. Thank you in advance for any help.
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE3, HD_CATEGORY.NAME AS CATEGORY, S.FULL_NAME AS SUBMITTER_NAME, IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.CUSTOM_FIELD_VALUE5, (LEFT(GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '
'),255)) AS SHORT_COMMENT FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 6) AND ((HD_TICKET.CUSTOM_FIELD_VALUE8 rlike 'Yes') AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))) GROUP BY HD_TICKET.ID ORDER BY ID
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
To get the user that created the ticket you can join to the initial "change" on the ticket:
JOIN HD_TICKET_CHANGE INITIALCHANGE on INITIALCHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID = (SELECT MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = HD_TICKET.ID)
This selects the change with the lowest ID from the HD_TICKET_CHANGE table for the given ticket ID. The user that created the ticket will be INITIALCHANGE.USER_ID, so you can now make another join to the user table:
JOIN USER C on C.ID = INITIALCHANGE.USER_ID
