/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: chucksteel 5 years ago
Red Belt
0

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
Posted by: chucksteel 5 years ago
Red Belt
0

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,

    Thank you. I will give these a shot and let you know. - grenaud 5 years ago
  • 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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ