MySQL Report for COUNT of work entries
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
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
GillySpy
11 years ago
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
Posted by:
WhitzEnd
11 years ago
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.
Posted by:
indemand
11 years ago
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