KACE Product Support

SELECT 'Queue', '# of Tickets Created', '# of Tickets Closed'
UNION
SELECT * FROM (
(SELECT 'Technical Support Center', COUNT(*) AS '# of Tickets Created' FROM HD_TICKET WHERE DATEDIFF(NOW(), CREATED) < 35 AND HD_QUEUE_ID = 3) as count1,
(SELECT COUNT(*) AS '# of Tickets Closed' FROM HD_TICKET  JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND (((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 30 DAY))) AND (HD_STATUS.NAME = 'closed'))) as count2,
(SELECT COUNT(*) AS NUMBER_OF_TICKETS,
    IF (
        DATE(TIME_OPENED) = DATE(TIME_CLOSED),
        CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,    IF (TIME_OPENED = '0', TIME_CLOSED,    TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'),
        CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                        SELECT
                            COUNT(*)
                        FROM
                            HD_SLA_HOLIDAYS
                        WHERE
                            (WEEKDAY(VALUE) < 5)
                        AND (
                        VALUE BETWEEN TIME_OPENED AND TIME_CLOSED
                        )
                    ))/COUNT(HD_TICKET.ID), 1))), ' Days')
        ) AS AVG_TIME_TO_CLOSE
FROM
    HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE
    HD_STATUS. NAME = 'Closed'
    AND TIME_CLOSED <> 0
    AND TIME_CLOSED > '2023-01-01' /*change the start date here*/
    AND TIME_CLOSED < '2023-01-31' /*change the end date here*/
        AND HD_TICKET.HD_QUEUE_ID IN (3) /*add queue numbers here separated by commas*/
) as count3
)