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;

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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.

 

 

Answered 07/24/2012 by: chucksteel
Red Belt

  • Thank you!
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by user request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity