Scripting

HELP ! how to generate report of all ticket queue with latest comments
select 

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

CONCAT(IF(Q.TICKET_PREFIX = 'TICK:','TICK:', Q.TICKET_PREFIX),LPAD(HD_TICKET.ID, 4, '0')) as 'Number' ,

HD_TICKET.TITLE as 'Title' ,

Q.NAME as 'Queue' ,

HD_CATEGORY.NAME as 'Category' ,

if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,

(select FULL_NAME from USER where ID= HD_TICKET.APPROVER_ID) as 'Approver' ,

HD_STATUS.NAME as 'Ticket Status' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Person in Charge' ,

HD_TICKET.DUE_DATE as 'Due Date' ,

HD_TICKET_CHANGE.TIMESTAMP,

HD_TICKET_CHANGE.COMMENT,

HD_TICKET.CUSTOM_FIELD_VALUE13 as 'IS Complete Time' ,

HD_TICKET.CUSTOM_FIELD_VALUE31 as 'Renewal Date'

from (HD_TICKET)

                        LEFT JOIN HD_STATUS on (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)

                        LEFT JOIN HD_IMPACT on (HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID)

                        LEFT JOIN HD_CATEGORY on (HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)

                        LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)

                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID

                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID

                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID 

                        LEFT OUTER JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 

                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

                        LEFT JOIN HD_TICKET_APPROVAL_PARENT HTAP on HTAP.HD_TICKET_ID = HD_TICKET.ID

                        LEFT JOIN HD_SERVICE_STATUS HSS on HD_TICKET.HD_SERVICE_STATUS_ID = HSS.ID

                        LEFT JOIN HD_SERVICE_TICKET HST on HST.ID = HD_TICKET.SERVICE_TICKET_ID

                        LEFT JOIN HD_SERVICE HS on HS.ID = HST.HD_SERVICE_ID

                        LEFT JOIN HD_SERVICE_TYPE HSTT on HSTT.ID = HS.HD_SERVICE_TYPE_ID

                        LEFT JOIN HD_TICKET_TEMPLATE on HD_TICKET_TEMPLATE.ID = HD_TICKET.TICKET_TEMPLATE_ID and HD_TICKET_TEMPLATE.QUEUE_ID = HD_TICKET.HD_QUEUE_ID 

where 1 = 1  AND 

                        (( HD_TICKET.HD_QUEUE_ID in (8,5,6,85,9,11,10,2,12,14,13) ) OR ( (HD_TICKET.OWNER_ID = 151)  or (HD_TICKET.APPROVER_ID = 151)  or (HD_TICKET.SUBMITTER_ID = 151)  or

                    EXISTS (

                        select *

                          from HD_TICKET_APPROVAL HTA,

                               HD_TICKET_APPROVAL_STAGE HTAS

                         where HTA.HD_TICKET_ID = HD_TICKET.ID

                           and HTA.HD_TICKET_ID = HTAS.HD_TICKET_ID

                           and HTA.USER_ID = 151

                           and HTA.APPROVAL = 'info'

                           and HTAS.APPROVAL_DATE = '0000-00-00 00:00:00'

                    ) ))  and ((HD_STATUS.STATE not in ('closed')  and (

            (!HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.SERVICE_TICKET_ID and HD_TICKET.IS_PARENT)

            or

            (HD_TICKET.SERVICE_TICKET_ID = 0 or (HD_TICKET.SERVICE_TICKET_ID and !HD_TICKET.IS_PARENT))

        ))

                                or (HSS.STATE not in ('closed')  and (HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.SERVICE_TICKET_ID and HD_TICKET.IS_PARENT))) and HD_TICKET_CHANGE.COMMENT is not NULL order by HD_TICKET_CHANGE.TIMESTAMP DESC;