KACE Product Support

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

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