K1000 service desk aging tickets
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
-
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. - chucksteel 6 years ago
-
Yes Chuck, I need the tickets that were open for more than a 7 days in each month for current year - aoh 6 years ago
-
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... - aoh 6 years ago
-
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... - aoh 6 years ago
Answers (2)
Please log in to answer
Posted by:
Druis
6 years ago
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
Posted by:
chucksteel
6 years ago
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.
Comments:
-
hi @chucksteel,
can you help to query with the aging status for below:
1) tickets aging between 25-31 days still not closed along with the ticket number, subject, date open, priority, assignee name.
2) ticket aging between 32-90 days still not closed along with the ticket number, subject, date open, priority, assignee name.
3) ticket aging more thatn 91days still not close along with the ticket number, subject, date open, priority, assignee name.
am planning to use this in PowerBI but not sure where to start. thanks .. - yustinus79 2 years ago
