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 )
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:
Please log in to answer
Posted by:
grayematter
10 years ago
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 10 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 10 years ago
-
There is only 1 queue in our system. - annleacock 10 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 10 years ago
