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
Answers (2)
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%.%")
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?