Closed Escalated Ticket to Closed Non Escalated Ticket Ratio
Can anyone point me in the right direction as to creating a query that would count all closed tickets that have been escalated vs closed tickets that have not been escalated... I'd like to get a ratio for each ticket owner of escalated/non-escalated... Should I be looking at a subquery, an IF statement, etc. What's the best route for such a thing?
SELECT U2.LOCATION, U2.FULL_NAME, COUNT(HD_TICKET.ESCALATED) AS 'Escalated' FROM HD_TICKET INNER JOIN USER U2 ON U2.ID = HD_TICKET.OWNER_ID WHERE HD_TICKET.ESCALATED != '0000-00-00 00:00:00' AND HD_TICKET.OWNER_ID <> 0 GROUP BY LOCATION, FULL_NAME ORDER BY LOCATION, FULL_NAME;
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
12 years ago
Here's a query that reports escalated and non-escalated ticket counts:
SELECT OWNER_ID, FULL_NAME, SUM(IF(ESCALATED='0000-00-00 00:00:00',1,0)) AS NOTESCALATED, SUM(IF(ESCALATED!='0000-00-00 00:00:00',1,0)) AS ESCALATED FROM HD_TICKET JOIN USER on USER.ID = HD_TICKET.OWNER_ID WHERE TIME_CLOSED > 0 GROUP BY OWNER_ID ORDER BY FULL_NAME
I used the following page as a guide to help writing it:
http://dev.mysql.com/tech-resources/articles/wizard/page3.html
That's a very useful technique that I'll have to remember.
Comments:
-
Thank you! - kpm8 12 years ago
