Someone help me to put department field on the query?

 

Select

  HD_QUEUE.NAME,

  HD_TICKET.ID,

  HD_TICKET.TITLE,

  HD_TICKET.DUE_DATE,

  Date_Format(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') As TIME_OPENED,

  Date_Format(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') As TIME_CLOSED,

  Concat(If(Time_To_Sec(HD_TICKET.TIME_CLOSED) >=

  Time_To_Sec(HD_TICKET.TIME_OPENED), To_Days(HD_TICKET.TIME_CLOSED) -

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

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

  Date_Format(AddTime("2000-01-01 00:00:00",

  Sec_To_Time(Time_To_Sec(HD_TICKET.TIME_CLOSED) -

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

  HD_TICKET.RESOLUTION As RESOLUTION,

  HD_PRIORITY.NAME As PRIORITY,

  HD_CATEGORY.NAME As CATEGORY,

  HD_STATUS.NAME As STATUS,

  HD_IMPACT.NAME As IMPACT,

  MACHINE.NAME As MACHINE_NAME,

  DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%M/%Y')AS MES_FECHAMENTO,

  IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < HD_PRIORITY.ESCALATION_MINUTES,'Yes','No') AS SLA,

  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,

  HD_PRIORITY.ESCALATION_MINUTES

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,

  HD_QUEUE

Where

  HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID And

  HD_STATUS.STATE = 'closed'

Order By

  IfNull((Select

    USER.FULL_NAME

  From

    USER

  Where

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

  HD_PRIORITY.ORDINAL,

  HD_CATEGORY.ORDINAL,

  HD_STATUS.ORDINAL,

  HD_IMPACT.ORDINAL

 

Thanks a lot

2 Comments   [ + ] Show Comments

Comments

  • Is Department one of your custom user fields or is it a custom field on the service desk?
  • The department is a custom field (custom 1) of LDAP that I want put on the report
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity