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:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Sorry Sumamalik, you didn't mention how.
Please log in to comment

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
Third Degree Green Belt

  • Thank you Jason. I will give it ago.
Please log in to comment

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

Please log in to comment
Answer this question or Comment on this question for clarity