Miscellaneous

SELECT

HD_TICKET.ID,

HD_TICKET.ID AS TICKNUM,

HD_TICKET.TITLE,

HD_TICKET.CUSTOM_FIELD_VALUE0 AS EMPLOYEE_NAME,

DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED,

DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED,

C.COMMENT,

C.DESCRIPTION, -- $description

GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n') ORDER BY H.ID DESC SEPARATOR '\n') HISTORY,

UPDATER.USER_NAME AS UPDATER_UNAME,

UPDATER.FULL_NAME AS UPDATER_FNAME,

UPDATER.EMAIL AS UPDATER_EMAIL,

IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,

OWNER.USER_NAME AS OWNER_UNAME,

OWNER.FULL_NAME AS OWNER_FNAME,

OWNER.EMAIL AS OWNER_EMAIL,

IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER,

SUBMITTER.USER_NAME AS SUBMITTER_UNAME,

SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,

SUBMITTER.EMAIL AS SUBMITTER_EMAIL,

SUBMITTER.WORK_PHONE AS SUBMITTER_PHONE,

P.NAME AS PRIORITY,

S.NAME AS STATUS,

I.NAME AS IMPACT,

CAT.NAME AS CATEGORY,

'externalhelpdesk@company.com' AS NEWTICKETEMAIL

FROM HD_TICKET

JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

AND C.ID=<CHANGE_ID>

JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID

JOIN HD_STATUS S ON S.ID=HD_STATUS_ID

JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID

JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID

LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

WHERE

C.DESCRIPTION LIKE 'TICKET CREATED%' AND

(

CAT.NAME LIKE '%Upgrades/Project Rollouts%'

)

GROUP BY HD_TICKET.ID

HAVING 1=1