/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

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.
Please log in to comment
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.
Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share