/build/static/layout/Breadcrumb_cap_w.png

Queue Report Isolation

Have a report we use for 1 queue and works fine. duplicated it for another queue to get open closed per month. it grabs closed correctly bu not the opened for just that queue


SELECT 

    OPEN.MONTH,

    OPEN.YEAR,

    COALESCE(OPEN.OPEN, 0) AS Opened,

    COALESCE(CLOSED.CLOSED, 0) AS "Closed"

FROM

    (SELECT 

        DATE_FORMAT(T.CREATED, '%M') AS MONTH,

            YEAR(T.CREATED) AS YEAR,

            COUNT(*) AS OPEN

    FROM

        HD_TICKET T

    GROUP BY MONTH , YEAR

    ORDER BY YEAR , MONTH) OPEN

        LEFT JOIN

    (SELECT 

        DATE_FORMAT(T.TIME_CLOSED, '%M') AS MONTH,

            YEAR(T.TIME_CLOSED) AS YEAR,

            COUNT(*) AS CLOSED

    FROM

        HD_TICKET T

    JOIN HD_QUEUE Q ON HD_QUEUE_ID = Q.ID 

    AND HD_QUEUE_ID = '4'

    JOIN HD_STATUS S ON HD_STATUS_ID = S.ID

        AND S.STATE = 'Closed'

    GROUP BY MONTH , YEAR

    ORDER BY YEAR , MONTH) CLOSED ON (OPEN.MONTH = CLOSED.MONTH

        AND OPEN.YEAR = CLOSED.YEAR)

WHERE  

    OPEN.YEAR = DATE_FORMAT(CURDATE(), '%Y')

ORDER BY STR_TO_DATE(OPEN.MONTH, '%M') ASC


0 Comments   [ + ] Show comments

Answers (2)

Posted by: jjayko 8 months ago
Orange Belt
0

so this is what i have and it works except it sends almost 10 emails each time

SELECT

HD_TICKET.CREATED,

HD_PRIORITY.NAME AS PRIORITY,

HD_TICKET.TITLE AS SUMMARY,

HD_TICKET.ID AS TICKNUM, -- $ticknum

HD_CATEGORY.NAME AS CATEGORY,

C.COMMENT,

C.DESCRIPTION,

S.FULL_NAME AS SUBMITTER_NAME,

U2.FULL_NAME As SUBMITTER_FULLNAME,

U2.EMAIL As SUBMITTER_EMAIL,

Q.NAME AS QUEUE_NAME, O.FULL_NAME AS OWNER_NAME,

HD_TICKET.ID as TOPIC_ID , CONCAT('TICK: ', HD_TICKET.ID) AS Number,

OLIST.EMAIL AS NEWTICKETEMAIL, -- $newticketemail

CAT.NAME AS CATEGORY, -- $category

HD_STATUS.NAME AS STATUS,

(SELECT 

            GROUP_CONCAT(USER.EMAIL

                    SEPARATOR ', ') AS ADDRESSLIST

        FROM

            LABEL

                INNER JOIN

            USER_LABEL_JT ON USER_LABEL_JT.LABEL_ID = LABEL.ID

                INNER JOIN

            USER ON USER.ID = USER_LABEL_JT.USER_ID

        WHERE

            LABEL.NAME = 'Network Notifications') AS GROUPMAIL

FROM

HD_TICKET

JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID  AND C.ID=<CHANGE_ID>

LEFT JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)

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

Left Join USER U2 On (U2.ID = HD_TICKET.SUBMITTER_ID)

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

JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID

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

LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_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_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID

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

/* group email */

JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID

JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID

JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID

/* queue */

JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID

WHERE

HD_TICKET.HD_QUEUE_ID = '4' and C.DESCRIPTION LIKE concat("%Changed ticket Queue from%to%.%")

Posted by: Hobbsy 9 months ago
Red Belt
0

I would suggest it may be because your join statement joins HD_TICKET on the Queue ID specifically, as opposed to placing the Queue ID into the WHERE statement?

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