Different times in UCCX database don't seem to add up?

[Rene.dejager1]

'Hi,
I am making some reports from the UCCX database. To examine the database I use SQuirrel.
In the documentation it states that the total connection time of an incoming call is found in:

contactcalldetail.connecttime

I assumed that this would equal the sum of the time spent in de CSQ queue + the time spent with the agent:

contactqueuedetail.queuetime + agentconnectiondetail.ringtime + agentconnectiondetail.talktime + agentconnectiondetail.holdtime

However i do see different outcomes, sometimes missing some seconds and sometime not.

An example of the output table:

sessionid	targetid	connecttime	queuetime	ringtime	talktime	holdtime	worktime
9,000,000,053	39	53	12	12	12	0	0
9,000,000,055	39	25	19	15	0	0	0
9,000,000,055	39	25	19	15	0	0	0
9,000,000,061	38	27	2	2	17	0	0
9,000,000,064	38	33	1	1	25	0	0
9,000,000,067	48	32	1	1	24	0	0
9,000,000,089	50	11	4	4	2	0	0
9,000,000,093	51	13	4	4	4	0	0
9,000,000,095	50	21	2	2	8	0	0
9,000,000,097	51	22	6	0	0	0	0
9,000,000,104	50	16	2	2	9	0	0
9,000,000,106	50	18	1	1	10	0	0
9,000,000,108	50	22	2	2	14	0	0
9,000,000,113	52	16	2	2	8	0	60
9,000,000,126	52	19	1	1	12	0	60
9,000,000,141	52	25	5	5	14	0	46
9,000,000,143	52	66	24	4	27	10	60
9,000,000,146	52	81	4	4	71	0	44

The query i build in squirrel

select contactqueuedetail.sessionid,targetid, connecttime,contactqueuedetail.queuetime,  agentconnectiondetail.ringtime, agentconnectiondetail.talktime, agentconnectiondetail.holdtime,agentconnectiondetail.worktime from contactqueuedetail join contactroutingdetail on contactroutingdetail.sessionid=contactqueuedetail.sessionid join contactcalldetail on contactqueuedetail.sessionid=contactcalldetail.sessionid join agentconnectiondetail on contactqueuedetail.sessionid=agentconnectiondetail.sessionid where contactcalldetail.startdatetime > '2016-09-01 00:00:00' and contactcalldetail.enddatetime < '2016-09-01 23:00:00'

Even when going through the tables by hand and selecting on the sessionid I get the same result.
All the calls made on that date are simple test calls, no overflow and just 1 agent logged in.

I am sure I am missing something but what?

[Ratish Kalliadan]

Hi,

Most of the Columns that your are showing in the table is already available as stock reports in CCX, can you check if the data you see and what is shown in the CCX reports are identical for comparison, you can check the reporting guide for which tables these stock reports are referring to, so that your getting the data from the correct source.

Regards,

Ratish

[Rene.dejager1]

Hi,

I looked for 9,000,000,061 in the various reports:

This is what i found:

Agentdetail report
talktime	17
duration	20

Detailed call by call ccdr report
talktime	17
queuetime	2
Endtime - starttime	27	9-1-2016  9:42:21	9/1/16 9:41:54 am

Detailed Call CSQ Agent Report
ringtime	2

Is the duration of an ACD call the Endtime - starttime (27 seconds) or is the duration of an ACD call talktime+ringtime+queuetime (17+4+4=21 seconds) although the agentdetail report shows 20 seconds (could be rounding error)

What is the difference between the 27 second duration and the 20 (21) second duration.

Either I am missing something or I don't understand something.

Regards,

René

[Ratish Kalliadan]

In Agent Detail report the Duration is sum of Talk time and Work time.

In CCDR report the start - End time is Queue time+ ring time+ Talk time+ Hold time+ Work time.

I think if your seeing a diff between Start- end vs the total duration then its a bug, let me check and get back.

Regards,

Ratish