/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 monthly report showing total tickets submited, total tickets closed by each ticket owner invluding percentage of the total tickets closed

09/02/2016 1194 views
I need help in with SQL query that can produce a report that shows the total tickets submited (open, closed, etc...), total tickets closed by each ticket owner, a column that will also sow the percentage of the tickets closed by each owner per month in the last year. Thank you in advance.
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Sorry Sumamalik, you didn't mention how.

Answer Chosen by the Author

0
It sounds like you're describing two different reports: one with overview/summary data and another with details by owner. Here is a report that might work for the first of those:
SELECT YEAR(CREATED) AS TicketYears,
       MONTH(CREATED) AS TicketMonths,
       COUNT(ID) AS "Opened",
       (SELECT COUNT(ID)
          FROM HD_TICKET
         WHERE     YEAR(TIME_CLOSED) = TicketYears
               AND MONTH(TIME_CLOSED) = TicketMonths)
          AS "Closed",
       (SELECT COUNT(ID)
          FROM HD_TICKET_CHANGE
         WHERE     YEAR(TIMESTAMP) = TicketYears
               AND MONTH(TIMESTAMP) = TicketMonths
               AND DESCRIPTION LIKE
                      "%Changed ticket Queue%")
          AS "Transferred",
(SELECT COUNT(ID)
          FROM HD_TICKET_CHANGE
         WHERE     YEAR(TIMESTAMP) = TicketYears
               AND MONTH(TIMESTAMP) = TicketMonths
               AND DESCRIPTION LIKE
                      "Updated%")
          AS "Updates"  
  FROM HD_TICKET
GROUP BY YEAR(CREATED), MONTH(CREATED)
Answered 09/09/2016 by: JasonEgg
Red Belt

  • Thank you Jason. I will give it ago.

All Answers

0
aoh

I just did a similar report using crystal reports. This will involve grouping, summing and parameters.
Answered 09/02/2016 by: sumamalik
White Belt

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