Call Record in Termination Call Detail Table

[ali.parvez]

I have noticed that every call has more than one entry in the Termination_Call_Detail table. Is there a way to single out the final entry of the call. I mean any flag set in the entry that states that this is the final row of the call or any other way around?

[gz_lucky_gzb]

select

count(distinct convert(varchar(12),RouterCallKeyDay + convert( varchar(12), RouterCallKey))

from Termination_Call_Detail

where

PeripheralCallType = 1

and Duration=0

[gz_lucky_gzb]

select

count(distinct convert(varchar(12),RouterCallKeyDay) + convert( varchar(12), RouterCallKey))

from Termination_Call_Detail

where

PeripheralCallType = 1

and Duration=0

[ali.parvez]

Can you please explain the specific reason for using the condition Duration=0 ?

Also, if I want NetQTime, RingTime, TalkTime, WorkTime, HoldTime for the call in your query, do I need to take the Sum of each column e.g. Sum(NetQTime) or simply select the column.

I would much appreciate your thoughts on this problem.

Many Thanks

[richard-ferguson]

Have you read through ICM Schema Help? The TCD.RouterCallKeySequenceNumber orders the rows in which the calls (not the TCD records) were created. The TCD.RCKSN with the highest number is the last leg of the call.

I have an SQL Query that gets oodles of duration detial and is sorted the way you seem to be asking about. My customers use it for cradle-to-grave tracking. I can't post it here because it's bigger than 4000 characters. I'll happily eMail directly 2 u if you want to try it and save time mucking about with it yourself.

Cheers

Fergy

[ali.parvez]

Hi Richard,

I would much appreciate if you can send me the query on the following email address

ali.parvez@gmail.com

Many Thanks,

Ali

[pgrosdidier]

Richard,

I have a need to get last leg of the call etc. I would appreciate a copy of your query emailed to me as well.

pgrosdidier@everestkc.net

Thank you,

Phil

[jwolfeld]

Richard, I think it would be generally useful for all if you would post your query as a text file attachment.

Thanks!

[richard-ferguson]

You're welcome.

It's in a 3kb zip as it's 8kb unzipped and won't fit in here. I'm going to Fiji on holiday tomorrow so I'll answer further queries after 6 Sept. In the past I've linked TCDs and RCDs with DateTime and RouterCallKey... but it gets confusing so I've settled on the method included in the zip file.

Cheers

Fergy

[richard-ferguson]

Instructions:

The Trace RCD and TCD 6.sql should work out of the box from 'Now' backwards by 5 minutes if you select _awdb as the database to query in SQL Query Analyser.

A bunch of the first columns line up above one another to ensure that details don't get confused. Most of the useful stuff is further to the right.

Once you have the default query working, you'll probably want to comment out lines 14 and 15 with -- and uncomment lines 11 and 12 for a Fixed Date Range.

This gives you a bunch of calls to look at for the date range specified.

When you've found a specific call to trace, modify these lines with the RCKDay and RtrCallKey you actually want:

SELECT @RCKDay = 147773

SELECT @RtrCallKey = 45229

...Then follow the cradle to grave call trace through.

As context, I use this query with an ICM 5.0 SR7 NAM system (yes, in weeny little New Zealand) where our Telco Intelligent Network can PreRoute calls directly to Agent DDIs from the PSTN. I don't have an ICM Enterprise system to try it on but assume it works just the same.

Cheers

Fergy

[abhijit75]

Richard,

I wasn't able to download the zip file you posted earlier. Could you email that to me, I wanted to take a look at what you had done. Thanks

akulkarni@agia.com

Thanks

Abhijit