/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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

Answer this question or Comment on this question for clarity

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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share