Background

CompressedUA contains statistics regarding:

• Calls to users with respect to particular ACD groups and hotlines
• Transfer calls made by users
• Transfer calls received by users

Information such as:

• The service number
• The ACD group
• The number of calls
  • Direct 
  • From a transfer
  • Transfered by agent
• Information on results
  
  • Answered
  • Busy
  • Caller Hangup
  • Rejected
• Times
  • Ring time
  • Talk time

is provided.

Note: because these statistics are aggragated, not quite as much information is available as from the call detail records. However, this aggregated data can be retained for a much longer time.

Parameters

Data is aggregated according to the settings of the parameters:

Query

Two tables are provided, CompressedA15 (15 minute time slices) and CompressedADay (day compression), aggregated according to the following query:

SELECT      (Various Aggregations)
FROM        JTELStats.StatisticsPartA a
LEFT JOIN   JTELStats.AcdStatisticsPartB acdb
    ON      a.ID = acdb.StatisticsPartAID
LEFT JOIN   JTELStats.StatisticsPartB b
    ON      acdb.ID = b.AcdStatisticsPartBID
WHERE       ( a.ClientsID = _ClientsID )
            AND
            ( a.dtCallStart BETWEEN _Timeslice_Begin AND TIMESTAMPADD( MINUTE, 15, _Timeslice_Begin ) )
            AND
            ( a.bOutbound = 0 )
GROUP BY    a.ID;

Partitioning and Aggregations 

When the data is aggregated, it is divided into several consistent partitions, according to the following logic. The resulting aggregations then either count a call, if a condition is met, or determine the time between two timestamps.

ServiceNumber_* Fields

Partitions all records only using fields from StatisticsPartA.

ACD_* Fields

Partitions records only for which an AcdStatisticsPartB record exists, and for which it is not a group transfer or group overflow.

The condition used is:

( AcdStatisticsPartB.ID IS NOT NULL ) AND ( AcdStatisticsPartB.AcdOriginGroupsID IS NULL )

Queue_* Fields

Further partitions records which have entered the ACD, only calls which enter the queue.

The additional condition used is:

( AcdStatisticsPartB.dtQueueStart IS NOT NULL )

Queue_Serviced_* Fields

Further partition on calls which entered the queue, only calls answered by an agent are considered (calls were definitely offhook). The condition used is:

The additional condition used is:

( StatisticsPartA.dtCallConnect IS NOT NULL ) AND ( AcdStatisticsPartB.dtAgentConnect IS NOT NULL )

Queue_NotServiced_* Fields

Further partition on calls which entered the queue, and were offhook, only calls not answered by an agent are considered. The condition used is:

( StatisticsPartA.dtCallConnect IS NOT NULL ) AND ( AcdStatisticsPartB.dtAgentConnect IS NULL )

These calls are further partitioned into Abandoned and Reject as follows:

a.bCallerHangup = 0  --> System hangup (Reject)
a.bCallerHangup <> 0  --> Caller hangup (Abandoned)

AgentCalls_*, GroupTransfer_*, AgentTransfer_*, NumberTransfer_* Fields

Only records which meet the following condition are considered:

( StatisticsPartB.ID IS NOT NULL )

I.e. where an agent or destination call was made.

The following partitions are used:

StatisticsPartB.StatisticsPartBOrigin IS NULL

( StatisticsPartB.StatisticsPartBOrigin IS NOT NULL )

AND

( StatisticsPartB.AcdGroupsID <> AcdStatisticsPartB.AcdGroupsID )

( StatisticsPartB.StatisticsPartBOrigin IS NOT NULL )

AND

( StatisticsPartB.AcdGroupsID = AcdStatisticsPartB.AcdGroupsID )

AND 

( StatisticsPartB.bTransferAgent <> 0 )

( StatisticsPartB.StatisticsPartBOrigin IS NOT NULL )

AND 

( StatisticsPartB.AcdGroupsID = AcdStatisticsPartB.AcdGroupsID )

AND 

( StatisticsPartB.bTransferExternalNumber <> 0 )

Further partitioning is according to the CONNRES field.

CompressedA15

This table contains compressed statistics for 15 minute intervals.

CompressedADay

This table contains compressed statistics for day intervals.

Fields

The field definitions are essentially the same, in both tables, only the aggregation intervals are different.