K1000 monthly report showing total tickets submited, total tickets closed by each ticket owner invluding percentage of the total tickets closed
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.
1 Comment
[ + ] Show comment
-
Sorry Sumamalik, you didn't mention how. - aoh 7 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
sumamalik
7 years ago
aoh
I just did a similar report using crystal reports. This will involve grouping, summing and parameters.
Posted by:
JasonEgg
7 years ago
Top Answer
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)
Comments:
-
Thank you Jason. I will give it ago. - aoh 7 years ago
