/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Trying to add two columns for Percentage values. 24 hours and 48 hours of closed tickets vs Total Open Tickets

08/21/2019 324 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

2

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ