/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 service desk aging tickets

03/12/2018 664 views
I would appreciate it if someone can help me with SQL code that will generate a report showing aging (7 days) not yet closed tickets per month current year:
Eg: Tickets older than 7 dsys
Jan-218  Feb-2018  Mar-2018
    20            12              17
I hope this is possible. Thank you in advance.
3 Comments   [ + ] Show comments

Comments

  • Do you want tickets that were open for more than seven days in each month? I think that is what you are asking for, but I'm confused by the "not yet closed" part.
    • Yes Chuck, I need the tickets that were open for more than a 7 days in each month for current year
  • Thank you Chuck. The not yet closed (status !=open) as I want to include the stalled state like waiting on 3rd party, on hold, etc...
  • Thank you Chuck. The not yet closed (status !=open) as I want to include the stalled state like waiting on 3rd party, on hold, etc...

All Answers

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

SELECT
(SELECT COUNT(HD_TICKET.ID) FROM HD_TICKET
WHERE timestampdiff(DAY, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) > 7
AND (HD_TICKET.HD_QUEUE_ID = 1)
AND (Month(HD_TICKET.CREATED) = 1)
) AS Jan,
(SELECT COUNT(HD_TICKET.ID) FROM HD_TICKET
WHERE timestampdiff(DAY, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) > 7
AND (HD_TICKET.HD_QUEUE_ID = 1)
AND (Month(HD_TICKET.CREATED) = 2)
) AS Feb,
(SELECT COUNT(HD_TICKET.ID) FROM HD_TICKET
WHERE timestampdiff(DAY, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) > 7
AND (HD_TICKET.HD_QUEUE_ID = 1)
AND (Month(HD_TICKET.CREATED) = 3)
) AS Mar

Answered 03/16/2018 by: Druis
Third Degree Green Belt

0
For a simple difference between when the ticket was created and when the ticket was closed, this will work:
SELECT concat(date_format(TIME_CLOSED, "%b"), " - ", year(TIME_CLOSED)) as "Month/Year", 
count(HD_TICKET.ID) as "Tickets Open More than 7 Days"
FROM ORG1.HD_TICKET
JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
and TIME_CLOSED != '0000-00-00 00:00:00'
and TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)) > 604800
and YEAR(CREATED) = YEAR(NOW())
GROUP BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
If you want to determine this based on the time tickets were in an actual "open" state and not including time when the ticket was in a "stalled" state, it will get much more difficult.
Answered 03/20/2018 by: chucksteel
Red Belt

Don't be a Stranger!

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

Sign up! or login

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