/build/static/layout/Breadcrumb_cap_w.png

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.

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
1
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))))
GROUP BY HD_TICKET.CATEGORY_ID
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

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