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

Comments

Please log in to comment

Answers

0
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


Answered 08/09/2017 by: chucksteel
Red Belt

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