Kace SQL Report to show all open tickets by week
Hello guys,
I want to have a report that shows how many open tickets we have had per week for the past 12 months. If I was looking at the data for the last week, it would have to show how many opens tickets we had even for just last week. This would include tickets that were created weeks before, but it would exclude tickets that were closed this week.
So far I have a SQL query for only this week. This only shows tickets that are not closed this week. Some of the tickets in the result were created weeks ago but they are not yet closed.
SELECT
COUNT(HD_TICKET.ID) AS NUMBER_OPENED,
Q.NAME
FROM
HD_TICKET
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
WHERE
Q.NAME = "North America" and HD_STATUS.NAME != "CLOSED"
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Hobbsy
7 years ago
I think you are looking for the impossible! If your question is correct, you want to know how many calls were in an open state last week, the week before etc? From a reporting perspective you would have needed to capture that figure at the time, much as we have to with our DASHboard. You can report how many calls were logged per week...calls with a log date in the date range, any status : and you can report how many calls were closed per week, calls that are closed and have a closed date within the date range. But the only way you have of getting close is to get all of the above figures and do the maths....
i.e. This morning we have 50 calls open
last week we logged 60 and closed 49, therefore our backlog has increased by the difference of 11 and last week was 61
etc etc but it will not be an exact science if you are able to delete tickets, as that will throw out the numbers.
