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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share