/build/static/layout/Breadcrumb_cap_w.png

Total Count Report for KACE SD Tickets

I've got the base for the report I am trying to achieve, but really need assistance with completing the report.  What I am trying to do is get a report that lists the SD ticket categories that were created in the 7 days and also have a total count of all tickets opened at the bottom. Below is the code that I have so far for the listing of the tickets opened in the last week by count, but I just can't see to get the second part right - the total count at the bottom.  Can someone please help?

SELECT HD_CATEGORY.NAME AS 'Category', COUNT(HD_TICKET.TITLE) as 'Count of Tickets'
FROM HD_TICKET, HD_CATEGORY
WHERE HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID AND (HD_TICKET.HD_QUEUE_ID = 1) AND HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY )  AND (HD_TICKET.TIME_OPENED > 0)

group by CATEGORY


UNION

Select 'TOTAL', count(HD_TICKET.ID) as mytotalcount from HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY )


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: grayematter 9 years ago
5th Degree Black Belt
1
Check your second query.  If the above is exactly what you have, you are missing two items.  Change
Select 'TOTAL', count(HD_TICKET.ID) as mytotalcount from HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY )
to
Select 'TOTAL', count(HD_TICKET.ID) as mytotalcount from HD_TICKET where HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY )
That should fix your report.


Comments:
  • Thank you so much for the prompt reply!
    I tried out the query change and it does return a result, but it is not the correct number so I must have screwed up somewhere. When I run the query it returns ticket totals for the categories, but the totals are wrong. Which then makes the total at the bottom wrong. I'm not sure how to check what is wrong. Perhaps if I start over? Need a list similar to the one at the bottom of this, just picking up tickets assigned to categories and how many tickets there were for that category. Need the count for the last 7 days with a total number of all tickets entered at the bottom.:
    Category Count of Tickets
    Access::AS400 Locked S1 3
    Access::AS400/LAN Locked 1
    Access::LAN Expired 1
    Access::LAN Locked 21
    Access::LAN Renew Domain 1
    Access::PGP 1
    Access::Registry 5
    Access::Terminate Employee 3
    Agent Tech::Agent 28
    Agent Tech::Telecommuter::Hardware 1
    AS400/Software::Software Support 28 - annleacock 9 years ago
    • If you have multiple queues, you want to also include the queue restriction in the second where clause (for the grand total). The query above gets the total for ALL queues. Adding that limitation, I got the results I expected from the query. - grayematter 9 years ago
      • There is only 1 queue in our system. - annleacock 9 years ago
      • This is what works for me. If your counts are not what you expect, maybe there is some criteria I'm not getting clear.

        SELECT
        HD_CATEGORY.NAME AS 'Category',
        COUNT(HD_TICKET.TITLE) as 'Count of Tickets'
        FROM
        HD_TICKET,
        HD_CATEGORY
        WHERE
        HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        AND (HD_TICKET.HD_QUEUE_ID = 1)
        AND HD_TICKET.CREATED >= (CURDATE() - INTERVAL 7 DAY)
        group by CATEGORY
        UNION Select
        'TOTAL', count(HD_TICKET.ID)
        FROM
        HD_TICKET,
        HD_CATEGORY
        WHERE
        HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        AND (HD_TICKET.HD_QUEUE_ID = 1)
        AND HD_TICKET.CREATED >= (CURDATE() - INTERVAL 7 DAY)

        To investigate any erroneous counts, you can compare the tickets you expect to the tickets actually included.

        SELECT
        HD_TICKET.ID as 'Ticket ID',
        HD_CATEGORY.NAME AS 'Category',
        HD_TICKET.TITLE as 'title'
        FROM
        HD_TICKET,
        HD_CATEGORY
        WHERE
        HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        AND (HD_TICKET.HD_QUEUE_ID = 1)
        AND HD_TICKET.CREATED >= (CURDATE() - INTERVAL 7 DAY)
        order by CATEGORY , HD_TICKET.ID - grayematter 9 years ago
Posted by: annleacock 9 years ago
Senior Yellow Belt
0
Worked brilliantly!  thanks!!!

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