/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Kace Service Desk SQL Report Question

02/27/2018 968 views

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

Comments


All Answers

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?
Answered 03/08/2018 by: JasonEgg
Red Belt

  • The Technically Correct method if possible. Thank you for your help.
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
Answered 03/08/2018 by: JasonEgg
Red Belt

  • Thank you! This is exactly what I was looking for.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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