Background

JTELStats2 contains two more compression routines:

CompressedACDB15 and CompressedACDBDay tables are provided which compress the statistics based on an ACD Groups view of the calls.

This means that for example, if a call is transferred from one group to another, it will be counted twice in the report.

This type of counting is useful, for example, when using some personell planning tools which require a group / queue based view of the data for predicting the number of required agents.

Parameters

Data is aggregated according to the settings of the parameters:

Query

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

SELECT      (Various Aggregations) 
FROM		JTELStats.AcdStatisticsPartB acdb
LEFT JOIN	JTELStats.StatisticsPartA a
	ON		acdb.StatisticsPartAID = a.ID
LEFT JOIN	JTELStats.StatisticsPartB b
	ON		acdb.ID = b.AcdStatisticsPartBID
WHERE		( a.ClientsID = _ClientsID )
			AND
			( acdb.dtGroupStart BETWEEN _Timeslice_Begin AND Timeslice_End_ )
GROUP BY	acdb.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.

Inbound and Outbound

Inbound: ACD_* Fields

Partitions all records using the field AcdGroupEntryReasonsID from AcdStatisticsPartB.

Inbound: 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 )

Inbound: 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 )

Inbound: 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)

Inbound: 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 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.

Outbound

Inbound and Outbound: Partitioning on CONNRES

CompressedACDB15

This table contains compressed statistics for 15 minute intervals.

CompressedACDBDay

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.