/build/static/layout/Breadcrumb_cap_w.png
08/21/2019 234 views

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

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

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.


Answered 08/22/2019 by: chucksteel
Red Belt

  • This is perfect. Thank you very much for your help