Report for tickets by Location
All of my queues have their location in custom field 1; how can I modify this report to group by custom field 1 instead of owner ID? This is a report that I use to show open and closed tickets by owner that I would love to modify for locations.
SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED FROM (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN FROM HD_TICKET T LEFT JOIN USER U ON T.OWNER_ID = U.ID GROUP BY OWNER_ID, MONTH, YEAR ORDER BY YEAR, MONTH) OPEN LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, 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' LEFT JOIN USER U ON T.OWNER_ID = U.ID GROUP BY OWNER_ID, MONTH, YEAR ORDER BY YEAR, MONTH) CLOSED ON (OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER ) WHERE OPEN.YEAR = date_format(curdate(), '%Y') ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER
1 Comment
[ + ] Show comment
-
Custom Field 1 is the equivalent of MySQL: HD_TICKET.CUSTOM_FIELD_VALUE0 so you will need to reference that in your code instead of the OWNER_ID in the GROUP BY statements. - h2opolo25 10 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
10 years ago
To show the currently open tickets and total closed tickets per location this query would work:
SELECT CUSTOM_FIELD_VALUE0,
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Open",
SUM(CASE WHEN TIME_CLOSED != '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Closed"
FROM ORG1.HD_TICKET
GROUP BY CUSTOM_FIELD_VALUE0
Comments:
-
BRILLIANT!!! Thank you so much! - mracine 10 years ago
