Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
35 lines (32 sloc) 1.33 KB
-- Request from ITNinja site
-- Breaks down tickets into how long it took them to be closed
SELECT YEAR(CREATED), WEEK(CREATED), P.NAME,
COUNT(T.ID) AS "Total Opened",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 3600
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "1 hour",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 7200 and 14400
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "2 to 4 hours",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 14400 and 86400
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "4 hr to 1 day",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_CLOSED = '0000-00-00 00:00:00'
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "Still Open"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 2
GROUP BY YEAR(CREATED), WEEK(CREATED), P.ID
You can’t perform that action at this time.