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
Answers (3)
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 years ago
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 years ago
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'