We are using Kace to determine First Call Resolution (FCR) by measuring the number of Work Entries are on a ticket.  In general, I would like a column to read as follows for each ticket:

IF <Total Work Entries for Ticket>=0 THEN "N/A"
ELSEIF <Total Work Entries for Ticket>=1 THEN "First Call Resolution"
ELSE "Multiple Call Resolution"

Currently using the following SQL Statement, but the first column, which should read the # of Work Entries is displaying the count for the entire Queue, not just the individual ticket of that row.

SELECT 
(SELECT COUNT(HD_WORK.ID) FROM HD_WORK) as Total_Work_Entries,
W.STOP as DATE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED,
TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP, IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP)))  DIV 60 AS RESPONSE_TIME,
HD_TICKET.ID, 
HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE4 AS Organization,
HD_TICKET.CUSTOM_FIELD_VALUE0 AS Cart,
HD_TICKET.DUE_DATE ,
HD_TICKET.CREATED,
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_TICKET.TIME_CLOSED,
HD_QUEUE.NAME as QUEUE,
HD_PRIORITY.NAME as PRIORITY, 
HD_CATEGORY.NAME as CATEGORY, 
HD_STATUS.NAME as STATUS, 
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,HD_WORK W)

left join HD_QUEUE on HD_QUEUE_ID = HD_QUEUE.ID
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 USER ON (USER.ID = HD_TICKET.OWNER_ID)

JOIN
 (SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
 FROM HD_TICKET_CHANGE C
 WHERE
 (SELECT COUNT(*)
 FROM HD_TICKET_CHANGE FILTER
 WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
 AND FILTER.ID <= C.ID) = 1
 AND C.TIMESTAMP <> 0
 ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = HD_TICKET.ID)
JOIN
 (SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
 FROM HD_TICKET_CHANGE C
 WHERE
 (SELECT COUNT(*)
 FROM HD_TICKET_CHANGE FILTER
 WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
 AND FILTER.ID <= C.ID) = 2
 AND C.TIMESTAMP <> 0
 ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = HD_TICKET.ID)

where W.HD_TICKET_ID = HD_TICKET.ID
  and isnull(W.VOIDED_BY)

order by HD_TICKET.ID DESC

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

5

I'll let you add in the other columns (custom fields and time data you want) but here's what I would do:

 select 
case when count(W.ID) = 0 then 'n/a'
when count(W.ID) =1 then 'FCR'
else 'MCR' end FCR_STATUS,
count(W.ID) FCR_CT,
T.ID "Ticket",
P.NAME "Priority",
S.NAME "Status",
CAT.NAME "Category",
Q.NAME "Queue",
ifnull(OWNER.USER_NAME,"Unassigned") "Owner",
ifnull(SUBMITTER.USER_NAME,"Unassigned") "Submitter"
from
HD_TICKET T
join HD_QUEUE Q on T.HD_QUEUE_ID = Q.ID
join HD_CATEGORY CAT on T.HD_CATEGORY_ID = CAT.ID
join HD_STATUS S on T.HD_STATUS_ID = S.ID
join HD_PRIORITY P on T.HD_PRIORITY_ID = P.ID
left join USER SUBMITTER on SUBMITTER.ID=SUBMITTER_ID
left join USER OWNER on OWNER.ID=OWNER_ID
left join HD_WORK W on T.ID=W.HD_TICKET_ID
group by T.ID
order by Q.ID,FCR_CT desc
Answered 01/25/2013 by: GillySpy
Seventh Degree Black Belt

Please log in to comment

Answers

0

I would be interested in seeing the final product of this when you get it done.  Please use the mark the question as answered link and paste the final SQL in the answer summary.

Answered 01/26/2013 by: WhitzEnd
Seventh Degree Black Belt

Please log in to comment
0

Thank you GillySpy!  I was able to merge your statement in mine.  Here is my final SQL Statement, Custom Fields can be edited for your Queue needs.

 

SELECT 

case when count(W.ID) = 0 then 'N/A'
	when count(W.ID) =1 then 'FCR'
	else 'MCR' end FCR_STATUS,
count(W.ID) FCR_CT,
        
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED,
TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP, IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP)))  DIV 60 AS RESPONSE_TIME,
T.ID, 
T.TITLE,
T.CUSTOM_FIELD_VALUE4 AS Organization,
T.CUSTOM_FIELD_VALUE0 AS Cart,
T.DUE_DATE,
T.CREATED,
CONCAT(IF(TIME_TO_SEC(T.TIME_CLOSED) >= TIME_TO_SEC(T.TIME_OPENED),
              TO_DAYS(T.TIME_CLOSED) - TO_DAYS(T.TIME_OPENED),
              TO_DAYS(T.TIME_CLOSED) - TO_DAYS(T.TIME_OPENED) - 1), 'd ', 
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(T.TIME_CLOSED)-TIME_TO_SEC(T.TIME_OPENED))),
              '%kh %im')) AS TIME_TO_CLOSE,       
T.TIME_CLOSED,
Q.NAME as QUEUE,
P.NAME as PRIORITY, 
CAT.NAME as CATEGORY, 
S.NAME as STATUS, 
ifnull((select FULL_NAME from USER where T.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where T.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME

FROM HD_TICKET T
	join HD_QUEUE Q on T.HD_QUEUE_ID = Q.ID
	join HD_CATEGORY CAT on T.HD_CATEGORY_ID = CAT.ID
	join HD_STATUS S on T.HD_STATUS_ID = S.ID
	join HD_PRIORITY P on T.HD_PRIORITY_ID = P.ID
        left join USER SUBMITTER on SUBMITTER.ID=SUBMITTER_ID
        left join USER OWNER on OWNER.ID=OWNER_ID
        left join HD_WORK W on T.ID=W.HD_TICKET_ID

JOIN
 (SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
 FROM HD_TICKET_CHANGE C
 WHERE
 (SELECT COUNT(*)
 FROM HD_TICKET_CHANGE FILTER
 WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
 AND FILTER.ID <= C.ID) = 1
 AND C.TIMESTAMP <> 0
 ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = T.ID)
JOIN
 (SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
 FROM HD_TICKET_CHANGE C
 WHERE
 (SELECT COUNT(*)
 FROM HD_TICKET_CHANGE FILTER
 WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
 AND FILTER.ID <= C.ID) = 2
 AND C.TIMESTAMP <> 0
 ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = T.ID)

where W.HD_TICKET_ID = T.ID and isnull(W.VOIDED_BY)

group by T.ID
order by T.ID DESC
Answered 01/26/2013 by: indemand
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share