Trying to add two columns for Percentage values. 24 hours and 48 hours of closed tickets vs Total Open Tickets
The following gets us the values we need, but we need to add two percentage fields. First field would be "within 1 day" / "Total Opened" *100. The second being "within 2 days" / Total Opened" *100. I just don't know how to add this logic to the query below. Any help would be greatly appreciated.
SELECT YEAR(TIME_CLOSED), P.NAME,
COUNT(T.ID) AS "Total Opened",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 86400
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "Within 1 Day",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 86401 and 172800
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "Within 2 Days"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 8
and YEAR(TIME_CLOSED) = YEAR(NOW())
GROUP BY YEAR(TIME_CLOSED), P.ID
Answers (1)
Top Answer
That looks like one of mine. Here's a query that uses case statements instead of the sub-selects to get the counts and percentages. It's a bit more elegant.
SELECT YEAR(TIME_CLOSED), P.NAME,
COUNT(T.ID) AS 'Total Opened',
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 86400 THEN 1 ELSE 0 END) AS 'Within 1 Day',
ROUND((SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 86400 THEN 1 ELSE 0 END)/COUNT(T.ID))*100,0) as 'Within 1 Day%',
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 86401 and 172800 THEN 1 ELSE 0 END) AS 'Within 2 Days',
ROUND((SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 86401 and 172800 THEN 1 ELSE 0 END)/COUNT(T.ID))*100, 2) AS 'Within 2 Days%'
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 8
and YEAR(TIME_CLOSED) = YEAR(NOW())
GROUP BY YEAR(TIME_CLOSED), P.ID
Let me know if that works for you.
Comments:
-
This is perfect. Thank you very much for your help - rafe1025 4 years ago
