In telecommunication sometimes it needs to analyze traffic to resolute dispute with parties; in this case either parties or both will provide their CDRs and will cross-check them.

CDR cross-checking criteria:

1. CDR should be matched with respect to call: start_time, a_party, b_party and duration
2. Some cases partners have differences in the call pattern for a_party or b_party; either party may prefix codes before the a_party or b_party number
3. We should update the call pattern for the CDRs in the table where applicable; or mention the condition in the SQL SELECT so the call pattern matched. For example if we have +88 as prefix on the a_party(8801755778899) but the partner doesn’t have the prefix(1755778899); we should update the a_party on our side by substr(a_party,4) so that it match the partner a_party.
4. As for the same call two switch might have difference in call start time as well as duration; we should find the CDRs that has the deviation up to a 2(two) seconds besides the exact matches; more than two seconds will lead to revenue loss or dispute

Method of CDR cross-check:

1. Load the CDR of the own in the table named as “CDR_OWN” from the regular CDR table used in operation.
2. Load the CDR of the partner in the table named as “CDR_OTHERS” by the Toad import utility.
3. Find out the matched CDR in both end (own and partner).
   • Find the CDR which has a 0(zero) second difference both in call start time and call duration
   • Insert the above matched CDR into the table “BOTH_MATCHED”
   • Find the CDR which has the 1(one) second difference both in call start time and call duration
   • Insert the above matched CDR into the table “BOTH_MATCHED”
   • Find the CDR which has the 2(two) second difference both in call start time and call duration
   • Insert the above matched CDR into the table “BOTH_MATCHED”
4. Find out the CDR that doesn’t match with the partner provided CDR

Prerequisites:

1. Create table named “CDR_OWN” with the fields start_time, a_party, b_party, duration; which will contain own CDRs
2. Create table named “CDR_OTHERS” with the fields start_time, a_party, b_party, duration; which will contain partner CDRs
3. Create table named “CDR_MATCHED” with the fields start_time, a_party, b_party, duration; which will contain the both parties matched CDR
4. Create table named “CDR_OWN_UNMATCHED” with the fields start_time, a_party, b_party, duration; which will contain the unmatched CDRs of own with partner

Oracle Tables Fields type:

1. start_time : varchar2(20) or Date (Format: “yyyymmddhh24miss”)
2. a_party : varchar2(100)
3. b_party : varchar2(100)
4. duration : number

Workaround:

Let’s say the CDRs to be cross-checked is ranging from dates ‘20170501000000’ and ‘20170531235959’.

1. Load the own CDR in the “CDR_OWN” table

INSERT INTO CDR_OWN (a.start_time,
 a.a_party,
 a.b_party,
 a.duration)
 SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM RAW_CDR
 WHERE start_time BETWEEN '20170501000000' AND '20170531235959';

2. Load the “CDR_OTHERS” by the Toad import utility.

3. Find the CDR which has a 0(zero) second difference both in call start time and call duration

INSERT INTO BOTH_MATCHED
 SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) = 0
 AND (a.duration - b.duration) = 0)

4. Delete the above matched CDRs from the CDR_OWN table, so that integrity of the records can be maintained

DELETE FROM CDR_OWN
 WHERE EXISTS
 (SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) = 0
 AND (a.duration - b.duration) = 0));

5. Find the CDR which has 1(one) second difference both in call start time and call duration

INSERT INTO BOTH_MATCHED
 SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) BETWEEN -1 AND 1
 AND (a.duration - b.duration) BETWEEN -1 AND 1);

6. Delete the above matched CDRs from the CDR_OWN table, so that integrity of the records can be maintained

DELETE FROM CDR_OWN
 WHERE EXISTS
 (SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.dur
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) BETWEEN -1 AND 1
 AND (a.duration - b.duration) BETWEEN -1 AND 1));

7. Find the CDR which has 2(two) seconds difference both in call start time and call duration

INSERT INTO BOTH_MATCHED
 SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) BETWEEN -2 AND 2
 AND (a.duration - b.duration) BETWEEN -2 AND 2);

8. Delete the above matched CDRs from the CDR_OWN table, so that integrity of the records can be maintained

DELETE FROM CDR_OWN
 WHERE EXISTS
 (SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.dur
 FROM CDR_OWN a
 JOIN CDR_OTHERS b
 ON (a.a_party = b.a_party)
 AND (a.b_party = b.b_party)
 AND a.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 AND b.start_time BETWEEN '20170501000000'
 AND '20170531235959'
 WHERE ( (a.start_time - b.start_time) BETWEEN -2 AND 2
 AND (a.duration - b.duration) BETWEEN -2 AND 2));

9. Now at this point we only have the unmatched CDRs in the CDR_OWN table; insert those records in the CDR_OWN_UNMATCHED table

INSERT INTO CDR_OWN_UNMATCHED (a.start_time,
 a.a_party,
 a.b_party,
 a.duration)
 SELECT a.start_time,
 a.a_party,
 a.b_party,
 a.duration
 FROM CDR_OWN;

10. Delete records from the CDR_OWN table or TRUNCATE the table for further operation

DELETE FROM CDR_OWN; or TRUNCATE TABLE CDR_OWN REUSE;

Note: The above SQL statements are applicable to Oracle database; implementing those in another database might need some modification based on the database using.