Voice

Home 

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'


Was this article useful?