/build/static/layout/Breadcrumb_cap_w.png

Create a report that will give me Open tickets by a specific department

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)

 

Z


Here is my SQL query:

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: Hobbsy 9 months ago
Red Belt
0

Surely you just need to add to your WHERE statement LABEL.ID = 45, if your joins are correct?

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ