/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: JasonEgg 6 years ago
Red Belt
0
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
Red Belt
0
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
 
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