I have a custom report (below) and would like to add the most recent comment and/or work on notes to it. Could anyone help me edit this SQL statement? 

 

Select

  HD_TICKET.ID,

  HD_TICKET.TITLE,

  HD_TICKET.CUSTOM_FIELD_VALUE2 As `Ticket Type`,

  Concat(If(Time_To_Sec(Now()) >= Time_To_Sec(HD_TICKET.TIME_OPENED),

  To_Days(Now()) - To_Days(HD_TICKET.TIME_OPENED), To_Days(Now()) -

  To_Days(HD_TICKET.TIME_OPENED) - 1), 'd ',

  Date_Format(AddTime("2000-01-01 00:00:00", Sec_To_Time(Time_To_Sec(Now()) -

  Time_To_Sec(HD_TICKET.TIME_OPENED))), '%kh %im')) As TIME_OPEN,

  HD_TICKET.MODIFIED As `Last Updated`,

  HD_TICKET.DUE_DATE,

  HD_CATEGORY.NAME As CATEGORY,

  HD_STATUS.NAME As STATUS,

  MACHINE.NAME As MACHINE_NAME,

  IfNull((Select

    USER.FULL_NAME

  From

    USER

  Where

    HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') As OWNER_NAME,

  (Select

    USER.FULL_NAME

  From

    USER

  Where

    HD_TICKET.SUBMITTER_ID = USER.ID) As SUBMITTER_NAME

From

  HD_TICKET Left Join

  HD_CATEGORY On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Left Join

  HD_STATUS On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID Left Join

  HD_PRIORITY On HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID Left Join

  HD_IMPACT On HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID Left Join

  MACHINE On HD_TICKET.MACHINE_ID = MACHINE.ID

Where

  (HD_STATUS.STATE = 'stalled') Or

  (HD_STATUS.STATE = 'opened')

Order By

  OWNER_NAME,

  `Last Updated`,

  TIME_OPEN Desc,

  HD_PRIORITY.ORDINAL,

  HD_CATEGORY.ORDINAL,

  HD_STATUS.ORDINAL,

  HD_IMPACT.ORDINAL
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I have used the following:

For the actual note, I didn't want the whole note because they could be too long so you could leave of fhte Cast if you wanted to:

CAST(HD_WORK.NOTE AS CHAR(50)) AS 'WORK NOTE ADDED'

 

and this had to be added in the joins:

 LEFT JOIN HD_WORK ON HD_WORK.HD_TICKET_ID=HD_TICKET.ID

Answered 03/31/2014 by: Vfrancois
Senior White Belt

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