Service Desk SQL Report Help
Hi All
I am trying to create a report on how many tickets were closed in a range of queues per 7 days.
I just want to show a total number of tickets closed as a value.
I have the report of closed tickets by owner working fine but the below report does not give correct values.
SELECT
COUNT(HD_TICKET.ID) AS NUMBER_CLOSED,
date_format(HD_TICKET.CREATED, '%D') AS DAY_OPENED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1 OR HD_TICKET.HD_QUEUE_ID = 9 OR HD_TICKET.HD_QUEUE_ID = 15 OR HD_TICKET.HD_QUEUE_ID = 6 OR HD_TICKET.HD_QUEUE_ID = 17) AND ((DATE(CURDATE() - INTERVAL 7 DAY) < DATE(HD_TICKET.TIME_CLOSED))) AND (HD_STATUS.NAME = 'Closed')
GROUP BY month(HD_TICKET.CREATED)
ORDER BY month(HD_TICKET.CREATED)
The problem being is that the report that shows all tickets closed per owner, when you add them it shows that 199 tickets were closed but the above report only shows that 169 were closed.
I am trying to create a report on how many tickets were closed in a range of queues per 7 days.
I just want to show a total number of tickets closed as a value.
I have the report of closed tickets by owner working fine but the below report does not give correct values.
SELECT
COUNT(HD_TICKET.ID) AS NUMBER_CLOSED,
date_format(HD_TICKET.CREATED, '%D') AS DAY_OPENED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1 OR HD_TICKET.HD_QUEUE_ID = 9 OR HD_TICKET.HD_QUEUE_ID = 15 OR HD_TICKET.HD_QUEUE_ID = 6 OR HD_TICKET.HD_QUEUE_ID = 17) AND ((DATE(CURDATE() - INTERVAL 7 DAY) < DATE(HD_TICKET.TIME_CLOSED))) AND (HD_STATUS.NAME = 'Closed')
GROUP BY month(HD_TICKET.CREATED)
ORDER BY month(HD_TICKET.CREATED)
The problem being is that the report that shows all tickets closed per owner, when you add them it shows that 199 tickets were closed but the above report only shows that 169 were closed.
3 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
grayematter
10 years ago
It took me a few minutes comparing the two queries, but I think I may have found the sources of your frustration.
The first is very minor with the difference in date comparisons. One query compares a timestamp (with the "NOW()" function) while the other uses a date (with "CURDATE()" function). This could account for a couple of tickets difference
The second is a bit more devious. One query filters on HD_STATUS.NAME = 'Closed' while the other filters on HD_STATUS.STATE = 'closed'. If you have a STATUS other than "Closed" that has a STATE of "closed", you could be throwing off your numbers here.
Take away from this: If you want the numbers to match, use the same criteria.
Comments:
-
Thank you very much for the help, keeping the date function the same and the HD_STATUS.STATE made all the difference, works perfectly now.
Again thank you for the help. - GoranK 10 years ago
I'm also not sure why you are selecting just DAY_OPENED and grouping by month? I would think you would want to select and group by both day and month. - grayematter 10 years ago
Below is what I use for getting closed tickets per owner.
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where (HD_STATUS.STATE = 'closed') and (HD_TICKET.HD_QUEUE_ID = 1 OR HD_TICKET.HD_QUEUE_ID = 9 OR HD_TICKET.HD_QUEUE_ID = 15 OR HD_TICKET.HD_QUEUE_ID = 6 OR HD_TICKET.HD_QUEUE_ID = 17) and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL - GoranK 10 years ago