5 Replies Latest reply: May 21, 2013 5:19 AM by 986570 RSS

    find interrelated calls among places

    986570
      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
          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
            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
              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
                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
                  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