Kace Service Desk SQL Report Question
I am hoping that someone will be able to help me with creating a couple of reports:
1. I need to create a report that will show me a count of all new tickets opened in a week
2. I need to create a report that will show me a count of all newtickets opened in a month
3. I need to create a report that will show me a count of all tickets closed in a week
4. I need to create a report that will show me a count of all tickets closed in a month
5. A report that will tell me the % of opened to closed tickets in a week, month, year
Any assistance would be greatly appreciated.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
JasonEgg
6 years ago
Open/closed during past # days:
select
COUNT(CASE WHEN (CREATED > (NOW() - INTERVAL [# OF DAYS] DAY)) THEN ID END) as 'Created',
COUNT(CASE WHEN (TIME_CLOSED > (NOW() - INTERVAL [# OF DAYS] DAY)) THEN ID END) as 'Closed'
from HD_TICKET
Percentage closed is a little more complicated since there's the quick and dirty way (ratio of total closed to total open) vs. the technically-correct method (percentage of the tickets that were opened then closed during the time period as compared to the tickets that were created and not closed during that same period). Which one do you want?
Comments:
-
The Technically Correct method if possible. Thank you for your help. - Jenafyre 6 years ago
Posted by:
JasonEgg
6 years ago
Percentage closed in week/month/year:
SELECT
CONCAT(ROUND((COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 WEEK)
AND TIME_CLOSED > (NOW() - INTERVAL 1 WEEK) THEN ID END)) /
(COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 WEEK) THEN ID END)) * 100,2),'%')
as 'Ratio Closed past week',
CONCAT(ROUND((COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 MONTH)
AND TIME_CLOSED > (NOW() - INTERVAL 1 MONTH) THEN ID END)) /
(COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 MONTH) THEN ID END)) * 100,2),'%')
as 'Ratio Closed past month',
CONCAT(ROUND((COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 YEAR)
AND TIME_CLOSED > (NOW() - INTERVAL 1 YEAR) THEN ID END)) /
(COUNT(CASE WHEN
CREATED > (NOW() - INTERVAL 1 YEAR) THEN ID END)) * 100,2),'%')
as 'Ratio Closed past year'
FROM HD_TICKET
Comments:
-
Thank you! This is exactly what I was looking for. - Jenafyre 6 years ago
