Help to create repport that counts unasigned cases
I am a neewbie and need some SQL-help for a report.
I am trying to make a report that displays number of cases thats opened/made the last "month" (the "month" last from the 11th to the 10th), how many of the cases that's closed/finished and number of cases that are unasigned to either a category or person (the default category is "Other :: Møterom"). I guess its easer to display if it is unasigned to a person.
Instead of making indivudial reports for different months a report for the whole year with separation for the different months is ok.
Answers (1)
This was a fun one:
SELECT
COUNT(HD_TICKET.ID) as "Tickets Opened",
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
SUM(CASE WHEN HD_CATEGORY.NAME = 'Unknown' THEN 1 ELSE 0 END) as "Unassigned Category",
SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL 1 MONTH))
The above selects from the previous month. The following shows tickets grouped by year and month:
SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,
COUNT(HD_TICKET.ID) as "Tickets Opened",
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
SUM(CASE WHEN HD_CATEGORY.NAME = 'Unknown' THEN 1 ELSE 0 END) as "Unassigned Category",
SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED)
Note that you'll need to change this to reflect your default category (mine is Unknown so look for that in the query). You'll also need to change the HD_TICKET.HD_QUEUE_ID = 2 to match your queue's ID.
Comments:
-
Thanks for Your quick response. I have changed the Query as you can see below. The months that we want to report on is from the 11th to the 10th. I know this problaby makes the SQL Query more Advanced but how do I do that?
SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,
COUNT(HD_TICKET.ID) as "Tickets Opened",
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
SUM(CASE WHEN HD_CATEGORY.NAME = 'Other :: Møterom' THEN 1 ELSE 0 END) as "Unassigned Category",
SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED) - MesaMe 9 years ago-
In order to limit the month reported you would need to add statements to the WHERE clause of the statement. For instance, you could add
MONTH(HD_TICKET.CREATED) = 10 to limit to tickets that were created in that month. If you wanted a range of dates you could use something like
HD_TICKET.CREATED BETWEEN '2013-10-01' and '2013-11-30'
To add these to the above statement you would place them after WHERE (HD_TICKET.HD_QUEUE_ID = 1) and the GROUP BY clause, so you would have something like this:
SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH, COUNT(HD_TICKET.ID) as "Tickets Opened", SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open", SUM(CASE WHEN HD_CATEGORY.NAME = 'Other :: Møterom' THEN 1 ELSE 0 END) as "Unassigned Category", SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker" FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)
and HD_TICKET.CREATED BETWEEN '2013-10-01' and '2013-11-30'
GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED)
Does that help? - chucksteel 9 years ago -
If you are referring to the "month" construct from http://www.itninja.com/question/reports-in-kace-that-counts-number-of-cases, you can substitute the same query segments for YEAR and MONTH. So change
YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,
to
CASE
-- december to january
when ( day(T.CREATED) >10 and month(T.CREATED) = 12 ) then 1
-- add to month
when ( day(T.CREATED) > 10) then month(T.CREATED) + 1
-- else just month
else month(T.CREATED) END as Month,
CASE
-- december to january
when ( day(T.CREATED) > 10 and month(T.CREATED) = 12 ) then year(T.CREATED) + 1
-- else just month
else year(T.CREATED) END as Year, - grayematter 9 years ago
As you are looking for counts, for unassigned tickets try using the following as a column in your query. Make sure that OWNER_NAME is in your Group By clause as well
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME - grayematter 9 years ago