Creating a report with multiple day intervals
I have 3 reports set up that list the amount of tickets that are older than 14, 30 and 60 days. I would like to combine those 3 intervals in to one report have the intervals labeled something like 14-29, 29-59, etc. is this even possible?
Here is the SQL that I am currently using.
SELECT HD_QUEUE.NAME as Queue, IFNULL(USER.USER_NAME,'-Unassigned-') as Owner,
count(HD_TICKET.ID) as "Open Tickets older than 14 Days"
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME NOT LIKE '%Closed%'
and HD_TICKET.TIME_OPENED < DATE_SUB(NOW(), INTERVAL 14 DAY)
and HD_TICKET.HD_QUEUE_ID in (2,3,4,10,12) /*add queue numbers here*/
GROUP BY QUEUE, OWNER
Answers (2)
Here's a report of open tickets per owner in the given time ranges. It includes a column for the queues where the owner has open tickets (in case they have tickets in multiple queues).
SELECT OWNER.FULL_NAME,
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) and NOW() THEN 1 ELSE 0 END) AS '0-15',
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_SUB(NOW(), INTERVAL 15 DAY) THEN 1 ELSE 0 END) AS '15-30',
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) and DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS '30-60',
GROUP_CONCAT(DISTINCT(HD_QUEUE.NAME)) AS 'Queues'
FROM HD_TICKET
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE HD_QUEUE_ID in (2,3,4,10,12)
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
GROUP BY OWNER.ID
ORDER BY OWNER.FULL_NAME
Comments:
-
Here's an update that includes tickets beyond the 60 day window:
SELECT OWNER.FULL_NAME,
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) and NOW() THEN 1 ELSE 0 END) AS '0-15',
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_SUB(NOW(), INTERVAL 15 DAY) THEN 1 ELSE 0 END) AS '15-30',
SUM(CASE WHEN HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) and DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS '30-60',
SUM(CASE WHEN HD_TICKET.CREATED < DATE_SUB(NOW(), INTERVAL 60 DAY) THEN 1 ELSE 0 END) AS '60+',
GROUP_CONCAT(DISTINCT(HD_QUEUE.NAME)) AS 'Queues'
FROM HD_TICKET
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE HD_QUEUE_ID in (2,3,4,10,12)
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
GROUP BY OWNER.ID
ORDER BY OWNER.FULL_NAME - chucksteel 5 years ago
Here is what I came up with:
SELECT HD_QUEUE.NAME,
SUM(CASE WHEN CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) and NOW() THEN 1 ELSE 0 END) AS '0-15',
SUM(CASE WHEN CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_SUB(NOW(), INTERVAL 15 DAY) THEN 1 ELSE 0 END) AS '15-30',
SUM(CASE WHEN CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) and DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS '30-60'
FROM ORG1.HD_QUEUE
JOIN HD_TICKET on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE HD_QUEUE.ID in (2,3,4,10,12)
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
GROUP BY HD_QUEUE.ID
ORDER BY HD_QUEUE.NAME
You could accomplish the same thing using sub select statements, too:
SELECT HD_QUEUE.NAME as 'Queue',
-- 0 to 14 days
(SELECT count(ID) FROM HD_TICKET
WHERE HD_QUEUE_ID = HD_QUEUE.ID
and HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 14 DAY) and NOW()
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
) as '0-14',
-- 15 to 29
(SELECT count(HD_TICKET.ID) FROM HD_TICKET
WHERE HD_QUEUE_ID = HD_QUEUE.ID
and HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_SUB(NOW(), INTERVAL 15 DAY)
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
) as '15-29',
-- 30 to 60
(SELECT count(HD_TICKET.ID) FROM HD_TICKET
WHERE HD_QUEUE_ID = HD_QUEUE.ID
and HD_TICKET.CREATED BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) and DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.TIME_CLOSED = '0000-00-00 00:00:00'
) as '30-60'
from HD_QUEUE
where HD_QUEUE.ID in (2,3,4,10,12) /*add queue numbers here*/
ORDER BY HD_QUEUE.NAME
This method has the advantage of showing all of the queues, even if there are not any tickets for the date ranges.
Comments:
-
-
Chucksteel,
These work great. I forgot to add in to my initial post that I would like to group them by owner and not queue. I suck at SQL and have been butchering your code for the last week trying to get it to work and have come up empty. Any thoughts or things you know of to point me in the right direction? - grenaud 5 years ago-
So you're looking for a report of open tickets per owner, not open tickets per queue? Do you still want it limited to just those queue IDs? If you want to include information on queue's and owners, that will be more complicated and the report will be messy. - chucksteel 5 years ago
-
chucksteel,
Yes, we are looking for a report by owner that shows the amount of tickets in each age group. The queue name would be nice, but not necessary. - grenaud 5 years ago
