This discussion is archived
5 Replies Latest reply: May 21, 2013 3:19 AM by 986570 RSS

find interrelated calls among places

986570 Newbie
Currently Being Moderated
Hi All,


Need to find interrelated calls among places

Create table cdranalysis (id number(15) primary key not null , mobile_number number(10), other_party number(10), call_type varchar2(10), placeid number(10), call_date_time date, duration number(15));

insert into cdranalysis values(1,9818764535 ,9897451236,'IN', 1,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
insert into cdranalysis values(2, 9897451236,9899917277,'OUT',2,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
insert into cdranalysis values(3, 9899917277,9818764535,'IN',3,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
insert into cdranalysis values(4,9899917277,9897451236,'OUT', 3,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
insert into cdranalysis values(5,9818764535,9899917277,'IN',1,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);
insert into cdranalysis values(6, 9899917277,9215478213,'IN',3,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
insert into cdranalysis values(7, 9897451236,9012451785,'OUT',2,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);


insert into cdranalysis values(8,9897451236,9818764535,'OUT', 2,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
insert into cdranalysis values(9,9899917277, 9897451236,'IN',3,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
insert into cdranalysis values(10,9818764535, 9899917277,'OUT',1,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
insert into cdranalysis values(11,9897451236,9899917277,'IN', 2,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
insert into cdranalysis values(12,9899917277,9818764535,'OUT',3,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);

insert into cdranalysis values(13,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
insert into cdranalysis values(14,9012451785, 9897451236,'IN',5,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);





Query criteria :

Need to find interrelated calls between placeid

Input:
Placeid Date
1 24-APR-13
2
3

output :

Mobile number otherparty placeid date_time duration
9818764535 9897451236 IN 1 24-APR-13 02:10:43 10
9897451236 9818764535 OUT 2 24-APR-13 02:10:43 10


9897451236 9899917277 OUT 2 24-APR-13 05:11:13 20
9899917277 9897451236 IN 3 24-APR-13 05:11:13 20

9899917277 9818764535 IN 3 24-APR-13 08:07:02 30
9818764535 9899917277 OUT 1 24-APR-13 08:07:02 30


9899917277 9897451236 OUT 3 24-APR-13 10:10:43 10
9897451236 9899917277 IN 2 24-APR-13 10:10:43 10

9818764535 9899917277 IN 1 24-APR-13 10:11:13 40
9899917277 9818764535 OUT 3 24-APR-13 10:11:13 40

Edited by: 983567 on May 4, 2013 4:22 AM
  • 1. Re: find interrelated calls among places
    Solomon Yakobson Guru
    Currently Being Moderated
    with t as (
               select  dense_rank() over(
                                         order by least(mobile_number,other_party),
                                                  greatest(mobile_number,other_party),
                                                  call_date_time,
                                                  duration
                                        ) interrelation_id,
                       count(*) over(
                                     partition by least(mobile_number,other_party),
                                                  greatest(mobile_number,other_party),
                                                  call_date_time,
                                                  duration
                                     ) cnt,
                       mobile_number,
                       other_party,
                       placeid,
                       call_date_time,
                       duration
                 from  cdranalysis
              )
    select  interrelation_id,
            mobile_number,
            other_party,
            placeid,
            call_date_time,
            duration
      from  t
      where cnt > 1
      order by interrelation_id
    /
    
    INTERRELATION_ID MOBILE_NUMBER OTHER_PARTY    PLACEID CALL_DATE_TIME        DURATION
    ---------------- ------------- ----------- ---------- ------------------- ----------
                   1    9897451236  9012451785          2 04/24/2013 18:21:04         67
                   1    9012451785  9897451236          5 04/24/2013 18:21:04         67
                   2    9215478213  9899917277          4 04/24/2013 12:17:02         25
                   2    9899917277  9215478213          3 04/24/2013 12:17:02         25
                   3    9897451236  9818764535          2 04/24/2013 02:10:43         10
                   3    9818764535  9897451236          1 04/24/2013 02:10:43         10
                   4    9899917277  9818764535          3 04/24/2013 08:07:02         30
                   4    9818764535  9899917277          1 04/24/2013 08:07:02         30
                   5    9899917277  9818764535          3 04/24/2013 10:11:13         40
                   5    9818764535  9899917277          1 04/24/2013 10:11:13         40
                   6    9897451236  9899917277          2 04/24/2013 05:11:13         20
    
    INTERRELATION_ID MOBILE_NUMBER OTHER_PARTY    PLACEID CALL_DATE_TIME        DURATION
    ---------------- ------------- ----------- ---------- ------------------- ----------
                   6    9899917277  9897451236          3 04/24/2013 05:11:13         20
                   7    9897451236  9899917277          2 04/24/2013 10:10:43         10
                   7    9899917277  9897451236          3 04/24/2013 10:10:43         10
    
    14 rows selected.
    
    SQL> 
    SY.
  • 2. Re: find interrelated calls among places
    986570 Newbie
    Currently Being Moderated
    But if i have to find call of same place


    insert into cdranalysis values(1,9818764535 ,9897451236,'IN', 1,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(2, 9897451236,9899917277,'OUT',2,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
    insert into cdranalysis values(3, 9899917277,9818764535,'IN',3,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
    insert into cdranalysis values(4,9899917277,9897451236,'OUT', 3,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(5,9818764535,9899917277,'IN',1,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);
    insert into cdranalysis values(6, 9899917277,9215478213,'IN',3,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(7, 9897451236,9012451785,'OUT',2,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);
    insert into cdranalysis values(8,9897451236,9818764535,'OUT', 2,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(9,9899917277, 9897451236,'IN',3,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
    insert into cdranalysis values(10,9818764535, 9899917277,'OUT',1,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
    insert into cdranalysis values(11,9897451236,9899917277,'IN', 2,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(12,9899917277,9818764535,'OUT',3,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);

    insert into cdranalysis values(13,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(14,9012451785, 9897451236,'IN',5,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);

    insert into cdranalysis values(15,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 20:07:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(16,9899917277, 9215478213,'IN',4,to_date('24-APR-13 20:07:02','dd-MON-yy hh24:mi:ss'),25);


    Input :
    placeid as 1,2,3,4

    and find internals of same placeid such as both mobile and otherparty reside on same place


    output :

    Mobile number otherparty placeid date_time duration
    9215478213 9899917277 OUT 4 24-APR-13 12:17:02 25
    9899917277 9215478213 IN 4 24-APR-13 12:17:02 25

    Edited by: 983567 on May 5, 2013 11:46 PM
  • 3. Re: find interrelated calls among places
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Is this sample data right?
    983567 wrote:
    ...
    insert into cdranalysis values(13,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    ...
    insert into cdranalysis values(15,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    Can there be 2 (or more calls) where all columns (except id) are the same?

    You may want something like this:
    SELECT     a.*
    FROM     cdranalysis  a
    JOIN     cdranalysis  b  ON   a.mobile_number     = b.other_party
                        AND  a.other_party     = b.mobile_number
                   AND  a.placeid          = b.placeid
                   AND  a.call_date_time     = b.call_date_time
    WHERE   a.placeid     IN (1, 2, 3, 4)          -- or whatever placeids you want
    ;
    or you may want SELECT DISTINCT , without the unique id.

     

    By the way:
    983567 wrote:
    Create table cdranalysis (id number(15) primary key not null ...
    The primary key is always NOT NULL. When you a column is the PRIMARY KEY, you don't need to say NOT NULL for that column.
  • 4. Re: find interrelated calls among places
    Solomon Yakobson Guru
    Currently Being Moderated
    with t as (
               select  dense_rank() over(
                                         order by least(mobile_number,other_party),
                                                  greatest(mobile_number,other_party),
                                                  placeid,
                                                  call_date_time,
                                                  duration
                                        ) interrelation_id,
                       count(*) over(
                                     partition by least(mobile_number,other_party),
                                                  greatest(mobile_number,other_party),
                                                  placeid,
                                                  call_date_time,
                                                  duration
                                     ) cnt,
                       mobile_number,
                       other_party,
                       placeid,
                       call_date_time,
                       duration
                 from  cdranalysis
              )
    select  interrelation_id,
            mobile_number,
            other_party,
            placeid,
            call_date_time,
            duration
      from  t
      where cnt > 1
      order by interrelation_id
    /
    
    INTERRELATION_ID MOBILE_NUMBER OTHER_PARTY    PLACEID CALL_DATE_TIME        DURATION
    ---------------- ------------- ----------- ---------- ------------------- ----------
                   4    9215478213  9899917277          4 04/24/2013 12:17:02         25
                   4    9899917277  9215478213          4 04/24/2013 12:17:02         25
                   4    9215478213  9899917277          4 04/24/2013 12:17:02         25
    
    SQL>
    SY.
    P.S. You have two duplicate rows (id 13 & 15). That's why my query returns 3 rows. Add dictinct if needed.
  • 5. Re: find interrelated calls among places
    986570 Newbie
    Currently Being Moderated
    The output of solution provided is merging both interrelated and same placeid calls

    Need to segregate
    1. interrelated among different placeid
    2. which are under same placeid


    Create table cdranalysis (id number(15) primary key not null , mobile_number number(10), other_party number(10), call_type varchar2(10), placeid number(10), call_date_time date, duration number(15));


    insert into cdranalysis values(1,9818764535 ,9897451236,'IN', 1,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(2, 9897451236,9899917277,'OUT',2,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
    insert into cdranalysis values(3, 9899917277,9818764535,'IN',3,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
    insert into cdranalysis values(4,9899917277,9897451236,'OUT', 3,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(5,9818764535,9899917277,'IN',1,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);
    insert into cdranalysis values(6, 9899917277,9215478213,'IN',3,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(7, 9897451236,9012451785,'OUT',2,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);
    insert into cdranalysis values(8,9897451236,9818764535,'OUT', 2,to_date('24-APR-13 02:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(9,9899917277, 9897451236,'IN',3,to_date('24-APR-13 05:11:13','dd-MON-yy hh24:mi:ss'),20);
    insert into cdranalysis values(10,9818764535, 9899917277,'OUT',1,to_date('24-APR-13 08:07:02','dd-MON-yy hh24:mi:ss'),30);
    insert into cdranalysis values(11,9897451236,9899917277,'IN', 2,to_date('24-APR-13 10:10:43','dd-MON-yy hh24:mi:ss'),10);
    insert into cdranalysis values(12,9899917277,9818764535,'OUT',3,to_date('24-APR-13 10:11:13','dd-MON-yy hh24:mi:ss'),40);

    insert into cdranalysis values(13,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 12:17:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(14,9012451785, 9897451236,'IN',5,to_date('24-APR-13 18:21:04','dd-MON-yy hh24:mi:ss'),67);

    insert into cdranalysis values(15,9215478213, 9899917277,'OUT',4,to_date('24-APR-13 20:07:02','dd-MON-yy hh24:mi:ss'),25);
    insert into cdranalysis values(16,9899917277, 9215478213,'IN',4,to_date('24-APR-13 20:07:02','dd-MON-yy hh24:mi:ss'),25);


    The output of solution provided is merging both interrelated and same tower calls

    Please help

    Edited by: 983567 on May 21, 2013 3:18 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points