/build/static/layout/Breadcrumb_cap_w.png

Report closed isolate queue

Trying to added parameters to isolate further by 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_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 (3)

Posted by: Hobbsy 2 weeks ago
Red Belt
0

Just add the Queue ID that you want to the WHERE statement.

To find out what the ID is go to Servicedesk>Settings Queues and hover over the queue you want, the ID will be shown in the bottom left corner.


Comments:
  • I get Error Code: 1054. Unknown column 'HD_QUEUE_ID' in 'where clause'
    tried top add it to select statement and get
    Error Code: 1052. Column 'HD_QUEUE_ID' in field list is ambiguous - jjayko 2 weeks ago
Posted by: Hobbsy 2 weeks ago
Red Belt
0

WHERE OPEN.YEAR = date_format(curdate(), '%Y') And 

HD_TICKET.QUEUE_ID = ‘7’


Comments:
  • Still get the error Error Code: 1054. Unknown column 'HD_TICKET.QUEUE_ID' in 'where clause'


    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_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') and
    HD_TICKET.QUEUE_ID = ‘3’
    ORDER BY STR_TO_DATE(OPEN.MONTH, '%M') ASC - jjayko 2 weeks ago
Posted by: jjayko 2 weeks ago
Senior Yellow Belt
0

was able to get it with changing

 (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 = '3'

    JOIN HD_STATUS S ON HD_STATUS_ID = S.ID

        AND S.STATE = 'Closed'

 
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