Ninja's,

I'm trying to create a report for the number of ticket sorted by category. The only problem is we have sub categories that the K1000 is also breaking on.

For instance in the report right now, I have:
Account Request::Service Account    5 tickets

Account Request::User Account 3 Tickets

Desktop::HardDrive 10

Desktop::Keyboard 7

What I'm trying to do is:
Account Request 8 tickets
Desktop  17 tickets

Basically I'm trying to get a total for each main category including the subcategories, but not have the subcategories in the report
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
This query will automatically use just the first part of the category so you don't have to hard code the top levels:

SELECT SUBSTRING_INDEX(HD_CATEGORY.NAME, "::", 1) AS TopCategory, COUNT(HD_TICKET.ID) FROM ORG1.HD_TICKETJOIN HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_IDWHERE HD_TICKET.HD_QUEUE_ID = 2GROUP BY TopCategory
Be sure to change the HD_QUEUE_ID to match your queue.

Answered 08/12/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

1
I have built something similar for us and was not able to get all the formatting 100% with the SQL but export it to excel and do the final touches there.

I home it will put you on the right track.

Below is my SQL Report

SELECT
    COUNT(T.ID) AS '# Tickets',
    T.CUSTOM_FIELD_VALUE13 AS Branch,
    SUM(CAST(HOUR(TIMEDIFF(STOP, START)) + IF((MINUTE(TIMEDIFF(STOP, START)) = 0),
            0,
            (MINUTE(TIMEDIFF(STOP, START)) / 60)) + ADJUSTMENT_HOURS
        AS DECIMAL (10 , 2 ))) as Hours_Worked,
    SUM(CASE
        WHEN T.CUSTOM_FIELD_VALUE9 REGEXP '^[0-9]' THEN "1"
        ELSE ""
    END) AS AfterHoursCall,
    CASE
        WHEN HD_CATEGORY.NAME LIKE 'Support::%' THEN 'Support'
        WHEN HD_CATEGORY.NAME LIKE 'Network Operations::%' THEN 'Network Operations'
        WHEN HD_CATEGORY.NAME LIKE 'Projects::%' THEN 'Project'
        WHEN HD_CATEGORY.NAME LIKE 'Training' THEN 'Training'
        WHEN HD_CATEGORY.NAME LIKE 'User Maintenance' THEN 'User Maintenance'
        WHEN HD_CATEGORY.NAME LIKE 'Hardware Maintenance' THEN 'Hardware Maintenance'
        ELSE "UNKNOWN"
    END AS Category
    FROM (HD_TICKET T, HD_WORK W)
        JOIN
    HD_CATEGORY ON (HD_CATEGORY.ID = T.HD_CATEGORY_ID)
WHERE
    W.HD_TICKET_ID = T.ID
        and isnull(W.VOIDED_BY)
        and T.HD_STATUS_ID = 11
        AND TIMESTAMP(T.TIME_CLOSED) > DATE_SUB(NOW(), INTERVAL 1 MONTH)
        AND T.HD_QUEUE_ID = 3
GROUP BY BRANCH , CATEGORY
Answered 08/11/2015 by: smalls
Senior Yellow Belt

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