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

Comments

  • 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.
Please log in to comment

Answers

1
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


Answered 10/22/2014 by: chucksteel
Red Belt

  • BRILLIANT!!! Thank you so much!
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share