/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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...
Please log in to comment

Answer this question or Comment on this question for clarity

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
Green Belt

Please log in to comment
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

Please log in to comment