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 )

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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.

Answered 06/13/2014 by: grayematter
Fourth Degree Black Belt

  • 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
    • 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.
      • There is only 1 queue in our system.
      • 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
Please log in to comment

Answers

0
Worked brilliantly!  thanks!!!
Answered 07/02/2014 by: annleacock
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity