Report Help: Year End Service Desk Reporting
My boss has asked me to come up with a report that shows the number of tickets per category per department for the current year. I can get part of this to work, but he is only wants the counts, not the details, which is a little more complex than my SQL Skills allow for. I can get things to break down by department and category, but I can't seem to get the report just say category and a number. For example, if my department is listed as SSS, and the category is PC, then I want the report to show 20 PC calls for SSS, without listing all of the calls.
Right now my report looks like this:
SELECT
HD_TICKET.CUSTOM_FIELD_VALUE0, HD_CATEGORY.NAME AS CATEGORY
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 2)
AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 1 YEAR))))
ORDER BY CUSTOM_FIELD_VALUE0
This gives me most of what I want, but I would like to just show a category and a number, EX: PC 40, instead of listing all tickets for a particular category.
Right now my report looks like this:
SELECT
HD_TICKET.CUSTOM_FIELD_VALUE0, HD_CATEGORY.NAME AS CATEGORY
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 2)
AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 1 YEAR))))
ORDER BY CUSTOM_FIELD_VALUE0
This gives me most of what I want, but I would like to just show a category and a number, EX: PC 40, instead of listing all tickets for a particular category.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
You need to add a count item to the selected columns and then group something. In this case adding count(HD_TICKET.ID) would give you a count of tickets and then group by category. The resulting query would then be:
SELECT
HD_TICKET.CUSTOM_FIELD_VALUE0, HD_CATEGORY.NAME AS CATEGORY, COUNT(HD_TICKET.ID)
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 2)
AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 1 YEAR))))
HD_TICKET.CUSTOM_FIELD_VALUE0, HD_CATEGORY.NAME AS CATEGORY, COUNT(HD_TICKET.ID)
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 2)
AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 1 YEAR))))
GROUP BY HD_TICKET.CATEGORY_ID
ORDER BY CUSTOM_FIELD_VALUE0
ORDER BY CUSTOM_FIELD_VALUE0
Comments:
-
How could I get the column header to display without the Custom_Field tag in front of it. I have tried to do a NAME AS, but that did not work. - tayana 7 years ago
-
To alias a column just use AS "Whatever", so
HD_TICKET.CUSTOM_FIELD_VALUE0 as Department
For you count you can use COUNT(HD_TICKET.ID) AS Count
or AS "Count of Tickets", AS "# Tickets" etc. - chucksteel 7 years ago
