SQL Statements for Historical Reports
The following tables list the SQL statements for each historical report.
AGENT_METRICS and AGENT_GROUP_METRICS SQL Statements
|
Report |
SQL Statements |
|
Average Busy Time |
MS SQL: CASE WHEN sum(V_CALLS_ANSWERED)>0 THEN sum(V_TOTAL_BUSY_TIME/60)/sum(V_CALLS_ANSWERED)/1000 WHEN sum(V_CALLS_ANSWERED)<=0 THEN 0 END as 'Average Busy Time, min'
JET SQL: IIF(sum(V_CALLS_ANSWERED)>0, sum(V_TOTAL_BUSY_TIME/60)/sum(V_CALLS_ANSWERED)/1000, 0) as 'Average Busy Time, min' |
|
Number of Accepted Transfers |
sum(V_TRANSFERS_ACCEPTED) as 'Number of Accepted Transfers' |
|
Number of Answered Returned Interactions |
sum(V_ANSWERED_RETURNS) as 'Number of Answered Returned Interactions' |
|
Number of Calls Answered |
sum(V_CALLS_ANSWERED) as 'Number of Calls Answered' |
|
Number of Originated Transfers |
sum(V_TRANSFERS_ORIGINATED) as 'Number of Originated Transfers' |
|
Number of Rejected Transfers |
sum(V_TRANSFERS_REJECTED) as 'Number of Rejected Transfers' |
|
Number of Returned Interactions |
sum(V_INT_RETURNED) as 'Number of Returned Interactions' |
|
Total After Call Work Time |
sum(V_WRAPUP_TIME/60)/1000 as 'Total After Call Work Time, min' |
|
Total Busy Time |
sum(V_TOTAL_BUSY_TIME/60)/1000 as 'Total Busy Time, min' |
|
Total Handling Time |
sum(V_TOTAL_HANDLING_TIME/60)/1000 as 'Total Handling Time, min' |
|
Total Held Time |
sum(V_HELD_TIME/60)/1000 as 'Total Held Time, min' |
|
Total Logon Time |
sum(V_LOGON_TIME/60)/1000 as 'Total Logon Time, min' |
|
Total Not Ready Time |
sum(V_NOT_READY_TIME/60)/1000 as 'Total Not Ready Time, min' |
|
Total Ready Time |
sum(V_READY_TIME/60)/1000 as 'Total Ready Time, min' |
|
Total Working Time |
sum(V_WORK_TIME/60)/1000 as 'Total Working Time, min' |
|
Work Timing Chart |
sum(V_TOTAL_BUSY_TIME/60)/1000 as 'Total Busy Time, min', sum(V_WRAPUP_TIME/60)/1000 as 'Total After Call Work Time, min', sum(V_READY_TIME/60)/1000 as 'Total Ready Time, min', sum(V_NOT_READY_TIME/60)/1000 as 'Total Not Ready Time, min' |
CC_METRICS SQL Statements
|
Report |
SQL Statements |
|
Average Abandon Time |
MS SQL: CASE WHEN sum(V_CALLS_ABANDONED)>0 THEN sum(V_ABANDON_TIME/60)/sum(V_CALLS_ABANDONED)/1000 WHEN sum(V_CALLS_ABANDONED)<=0 THEN 0 END as 'Average Abandon Time, min'
JET SQL: IIF(sum(V_CALLS_ABANDONED)>0, sum(V_ABANDON_TIME/60)/sum(V_CALLS_ABANDONED)/1000, 0) as 'Average Abandon Time, min' |
|
Average Busy Time |
MS SQL: CASE WHEN sum(V_CALLS_ANSWERED)>0 THEN sum(V_TOTAL_BUSY_TIME/60)/sum(V_CALLS_ANSWERED)/1000 WHEN sum(V_CALLS_ANSWERED)<=0 THEN 0 END as 'Average Busy Time, min'
JET SQL: IIF(sum(V_CALLS_ANSWERED)>0, sum(V_TOTAL_BUSY_TIME/60)/sum(V_CALLS_ANSWERED)/1000, 0) as 'Average Busy Time, min' |
|
Average Time in Queue |
MS SQL: CASE WHEN sum(V_CALLS_QUEUED)>0 THEN sum(V_TOTAL_QUEUED_TIME/60)/sum(V_CALLS_QUEUED)/1000 WHEN sum(V_CALLS_QUEUED)<=0 THEN 0 END as 'Average Time in Queue, min'
JET SQL: IIF(sum(V_CALLS_QUEUED)>0, sum(V_TOTAL_QUEUED_TIME/60)/sum(V_CALLS_QUEUED)/1000, 0) as 'Average Time in Queue, min' |
|
Calls Queued |
sum(V_CALLS_QUEUED) as 'Calls Queued' |
|
Number of Accepted Transfers |
sum(V_TRANSFERS_ACCEPTED) as 'Number of Accepted Transfers' |
|
Number of Answered Returned Interactions |
sum(V_ANSWERED_RETURNS) as 'Number of Answered Returned Interactions' |
|
Number of Calls Abandoned |
sum(V_CALLS_ABANDONED) as 'Number of Calls Abandoned' |
|
Number of Calls Answered |
sum(V_CALLS_ANSWERED) as 'Number of Calls Answered' |
|
Number of Calls Received |
sum(V_CALLS_RECEIVED) as 'Number of Calls Received' |
|
Number of Calls Short-Abandoned |
sum(V_CALLS_SHORT_ABANDONED) as 'Number of Calls Short-Abandoned' |
|
Number of Disconnected in IVR Interactions |
sum(V_INT_IVR_DISCONNECTED) as 'Number of Disconnected in IVR Interactions' |
|
Number of Originated Transfers |
sum(V_TRANSFERS_ORIGINATED) as 'Number of Originated Transfers' |
|
Number of Rejected Transfers |
sum(V_TRANSFERS_REJECTED) as 'Number of Rejected Transfers' |
|
Number of Returned Interactions |
sum(V_INT_RETURNED) as 'Number of Returned Interactions' |
|
Percent Calls Abandoned |
MS SQL: CASE WHEN sum(V_CALLS_QUEUED)>0 THEN sum(V_CALLS_ABANDONED)*100/sum(V_CALLS_QUEUED) WHEN sum(V_CALLS_QUEUED)<=0 THEN 0 END as 'Percent Calls Abandoned'
JET SQL: IIF(sum(V_CALLS_QUEUED)>0, sum(V_CALLS_ABANDONED)*100/sum(V_CALLS_QUEUED), 0) as 'Percent Calls Abandoned' |
|
Total Handling Time |
sum(V_CALLS_ANSWERED) as 'Number of Calls Answered', sum(V_CALLS_ABANDONED) as 'Number of Calls Abandoned', sum(V_CALLS_SHORT_ABANDONED) as 'Number of Calls Short-Abandoned' |
|
Total Handling Time |
sum(V_SERVICE_FACTOR*V_CALLS_ANSWERED)/sum(V_CALLS_ANSWERED) as 'Service Factor' |
|
Total After Call Work Time |
sum(V_WRAPUP_TIME/60)/1000 as 'Total After Call Work Time, min' |
|
Total Busy Time |
sum(V_TOTAL_BUSY_TIME/60)/1000 as 'Total Busy Time, min' |
|
Total Handling Time |
sum(V_TOTAL_HANDLING_TIME/60)/1000 as 'Total Handling Time, min' |
|
Total Held Time |
sum(V_HELD_TIME/60)/1000 as 'Total Held Time, min' |
|
Total Logon Time |
sum(V_LOGON_TIME/60)/1000 as 'Total Logon Time, min' |
|
Total Not Ready Time |
sum(V_NOT_READY_TIME/60)/1000 as 'Total Not Ready Time, min' |
|
Total Ready Time |
sum(V_READY_TIME/60)/1000 as 'Total Ready Time, min' |
|
Total Working Time |
sum(V_WORK_TIME/60)/1000 as 'Total Working Time, min' |
|
Work Timing Chart |
sum(V_TOTAL_BUSY_TIME/60)/1000 as 'Total Busy Time, min', sum(V_TOTAL_WRAPUP_TIME/60)/1000 as 'Total After Call Work Time, min', sum(V_READY_TIME/60)/1000 as 'Total Ready Time, min', sum(V_NOT_READY_TIME/60)/1000 as 'Total Not Ready Time, min' |
SERVICE_METRICS SQL Statements
|
Report |
SQL Statements |
|
Average Time in Queue |
MS SQL: CASE WHEN sum(V_CALLS_QUEUED)>0 THEN sum(V_TOTAL_QUEUED_TIME/60)/sum(V_CALLS_QUEUED)/1000 WHEN sum(V_CALLS_QUEUED)<=0 THEN 0 END as 'Average Time in Queue, min'
JET SQL: IIF(sum(V_CALLS_QUEUED)>0, sum(V_TOTAL_QUEUED_TIME/60)/sum(V_CALLS_QUEUED)/1000, 0) as 'Average Time in Queue, min' |
|
Calls Queued |
sum(V_CALLS_QUEUED) as 'Calls Queued' |
|
Number of Accepted Transfers |
sum(V_TRANSFERS_ACCEPTED) as 'Number of Accepted Transfers' |
|
Number of Calls Abandoned |
sum(V_CALLS_ABANDONED) as 'Number of Calls Abandoned' |
|
Number of Calls Answered |
sum(V_CALLS_ANSWERED) as 'Number of Calls Answered' |
|
Number of Calls Short-Abandoned |
sum(V_CALLS_SHORT_ABANDONED) as 'Number of Calls Short-Abandoned' |
|
Number of Originated Transfers |
sum(V_TRANSFERS_ORIGINATED) as 'Number of Originated Transfers' |
|
Number of Rejected Transfers |
sum(V_TRANSFERS_REJECTED) as 'Number of Rejected Transfers' |
|
Percent Calls Abandoned |
MS SQL: CASE WHEN sum(V_CALLS_QUEUED)>0 THEN sum(V_CALLS_ABANDONED)*100/sum(V_CALLS_QUEUED) WHEN sum(V_CALLS_QUEUED)<=0 THEN 0 END as 'Percent Calls Abandoned'
JET SQL: IIF(sum(V_CALLS_QUEUED)>0, sum(V_CALLS_ABANDONED)*100/sum(V_CALLS_QUEUED), 0) as 'Percent Calls Abandoned' |
|
Total Handling Time |
sum(V_CALLS_ANSWERED) as 'Number of Calls Answered', sum(V_CALLS_ABANDONED) as 'Number of Calls Abandoned', sum(V_CALLS_SHORT_ABANDONED) as 'Number of Calls Short-Abandoned' |
|
Total Handling Time |
sum(V_SERVICE_FACTOR*V_CALLS_ANSWERED)/sum(V_CALLS_ANSWERED) as 'Service Factor' |
|
Total Working Time |
sum(V_WORK_TIME/60)/1000 as 'Total Working Time, min' |