SQL Report Request: First Level Resolution
Hello fellow KACE administrators/enthusiasts!
If you saw my other recent question you will all know that my SQL skills are insufficient to program the following request. Would someone here with further developed SQL skills help me in this endeavor? Requesting a report that will show a count of total tickets that were closed over the last month, show a count for how many of those tickets were closed (owner) by three specific people (Maxwell, Joshua, and Brian), and show a count for the rest of the tickets that were closed by anyone else BUT those three people. I am also not looking for any specific information about each ticket, just a number (count) for each row. I have a similar report that shows First Call Resolution that I would like to model it after if possible. I will put the code from my First Call Resolution script and a screen shot of it below.
SELECT
Metric, Totals, Percentage
FROM
((select
1 AS ORD,
'Tickets Created' as Metric,
count(HD_TICKET_CHANGE.ID) as Totals,
'NA' as Percentage
from
HD_TICKET_CHANGE
inner join HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
where
description like 'Ticket Created%' and timestamp > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
2 AS ORD,
'Tickets Closed' as Metric,
count(HD_TICKET.ID) as Totals,
'NA' as Percentage
from
HD_TICKET
where
TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
3 AS ORD,
'Tickets Currently Open' as Metric,
count(HD_TICKET.ID) as Totals,
'NA' as Percentage
from
HD_TICKET
inner join HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
where
HD_STATUS.STATE <> 'closed' and HD_TICKET.HD_QUEUE_ID <> 2) union (select
4 AS ORD,
'First Call Resolution: Yes' as Metric, COUNT(ID) AS Totals, (COUNT(ID) * 100.0 / (SELECT COUNT(ID) from
HD_TICKET
WHERE
TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2)) as Percentage
from
HD_TICKET
WHERE
CUSTOM_FIELD_VALUE3 = 'Yes' and TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
5 AS ORD,
'First Call Resolution: No' as Metric, COUNT(ID) AS Totals, (COUNT(ID) * 100.0 / (SELECT COUNT(ID) from
HD_TICKET
WHERE
TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2)) as Percentage
from
HD_TICKET
WHERE
CUSTOM_FIELD_VALUE3 = 'No' and TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
6 AS ORD,
'First Call Resolution: Not Set' as Metric, COUNT(ID) AS Totals, (COUNT(ID) * 100.0 / (SELECT COUNT(ID) from
HD_TICKET
WHERE
TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2)) as Percentage
from
HD_TICKET
WHERE
CUSTOM_FIELD_VALUE3 <> 'Yes' and CUSTOM_FIELD_VALUE3 <> 'No' and TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2)) TMP
ORDER BY ORD;
Sorry for it being so fuzzy, I tried to upload the picture as a JPEG and PNG with both of them losing almost all quality. Thank you for your support!
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
MAXintosh
9 years ago
Top Answer
Hello ninjas! If anyone is interest I have found a solution to my problem and wanted to share it with everyone. We have 30+ people in our IT department with 3 being in the Help Desk (level 1) and we wanted to find First Level Resolution (tickets completed by Help Desk vs everyone else). Now, this report will only be around 90-95% accurate because of unique situations. An example being if a ticket was created and closed by the same same person what wasn't on the Help Dsek. To account for all the situations would require some crazy logic that I didn't have the time to get into. The main logic of the report goes like this: Out of all tickets closed in the past year, how many were completed by people from the Help Desk vs all other IT. Since all calls are channeled through the Help Desk this proved accurate enough for what we need. To beef up the report a bit, it shows a count for tickets created, tickets closed, and tickets currently open. Also shows a percentage too!
Hope you enjoy! *This report originated from here from someone (forgot who, sorry) and was built upon by myself*
SELECT Metric, Totals, PercentageFROM((select 1 AS ORD,'Tickets Created' as Metric,count(HD_TICKET_CHANGE.ID) as Totals,'NA' as PercentagefromHD_TICKET_CHANGEinner join HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_IDwheredescription like 'Ticket Created%' and timestamp > date_sub(now(), interval 12 month)) union (select 2 AS ORD,'Tickets Closed' as Metric,count(HD_TICKET.ID) as Totals,'NA' as PercentagefromHD_TICKETINNER JOIN USER U1 on U1.ID = HD_TICKET.OWNER_IDwhere(TIME_CLOSED > date_sub(now(), interval 12 month))) union (select 3 AS ORD,'Tickets Currently Open' as Metric,count(HD_TICKET.ID) as Totals,'NA' as PercentagefromHD_TICKETinner join HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.IDwhereHD_STATUS.STATE <> 'closed') union (select 4 AS ORD, 'Help Desk Unite! (Closed by either Maxwell, Joshua, or Brian)' as Metric, COUNT(HD_TICKET.ID) AS Totals, (COUNT(HD_TICKET.ID) * 100.0 / (SELECT COUNT(HD_TICKET.ID) fromHD_TICKETWHERETIME_CLOSED > date_sub(now(), interval 12 month))) as PercentagefromHD_TICKETINNER JOIN USER U1 on U1.ID = HD_TICKET.OWNER_IDWHERE(U1.FULL_NAME like 'Joshua Linscott' or U1.FULL_NAME like 'Maxwell Morin' or U1.FULL_NAME like 'Brian LeComte') and (TIME_CLOSED > date_sub(now(), interval 12 month))) UNION(select 5 AS ORD,'All Other IT (Closed by anyone else)' as Metric, COUNT(HD_TICKET.ID) AS Totals, (COUNT(HD_TICKET.ID) * 100.0 / (SELECT COUNT(HD_TICKET.ID) fromHD_TICKETWHERETIME_CLOSED > date_sub(now(), interval 12 month))) as PercentagefromHD_TICKETINNER JOIN USER U1 on U1.ID = HD_TICKET.OWNER_IDWHERE(U1.FULL_NAME not like 'Joshua Linscott' and U1.FULL_NAME not like 'Maxwell Morin' and U1.FULL_NAME not like 'Brian LeComte') and (TIME_CLOSED > date_sub(now(), interval 12 month))) union (select6 AS ORD,'Owner Not Set' as Metric, COUNT(HD_TICKET.ID) AS Totals, (COUNT(HD_TICKET.ID) * 100.0 / (SELECT COUNT(HD_TICKET.ID) fromHD_TICKETWHERETIME_CLOSED > date_sub(now(), interval 12 month))) as PercentagefromHD_TICKETINNER JOIN USER U1 on U1.ID = HD_TICKET.OWNER_IDWHERE(U1.FULL_NAME like 'Unassigned' or U1.FULL_NAME is NULL) and (TIME_CLOSED > date_sub(now(), interval 12 month)))) TMPORDER BY ORD;