/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: GillySpy 11 years ago
7th Degree Black Belt
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
Posted by: WhitzEnd 11 years ago
7th Degree Black Belt
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.

Posted by: indemand 11 years ago
Senior Yellow Belt
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ