Miscellaneous

I’m trying to create a report that will give me Open tickets by a specific department and list the users within that department.

Criteria: Status = Opened, Label (Department) = Finance (This may also apply to other departments in the future)

SELECT DISTINCT

              HD_TICKET.ID,

              HD_TICKET.TITLE,

              HD_TICKET.DUE_DATE,

              O.FULL_NAME AS OWNER_NAME,

              IF(HD_TICKET.HD_USE_PROCESS_STATUS

                             AND HD_TICKET.IS_PARENT, HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) AS STATUS_NAME,

              HD_TICKET.CC_LIST,

              Q.NAME AS QUEUE_NAME,

              HD_TICKET.CUSTOM_FIELD_VALUE4,

S.FULL_NAME AS SUBMITTER_NAME

FROM HD_TICKET

LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)

LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS

AND HD_TICKET.HD_SERVICE_STATUS_ID

AND HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID

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

JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID

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

LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)

JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = O.ID

JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID

WHERE (HD_STATUS.NAME = 'Opened')

  ORDER  BY title