1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Retrieval of data on basis of two consecutive rows

Discussion in 'SQL PL/SQL' started by pankajmaurya55, Jul 4, 2015.

  1. My table structure is as follows:

    Calling_number, Called_number, Called_date_time, duration_of_call

    I want to retrieve data on the following basis:
    1) All the call where calling no and called no were same from previous call between an interval of 3 mins and having duration <10 secs.
    i.e calling_number called_number Date_time duration
    1) 9XXXXXXXX 9YYYYYYYY 2015-03-01 02:02:05 8
    2) 9XXXXXXXX 9YYYYYYYY 2015-03-01 02:03:00 4
    3) 9XXXXXXXX 9YYYYYYYY 2015-03-03 12:03:00 2
    4) 9XXXXYYYY 9YYYYYYXX 2015-03-01 12:03:00 4
    5) 9XXXXXXXX 9YYYYYYYY 2015-03-01 12:03:45 45
    6) 9XXXXXYYY 9YYYYYYXX 2015-03-01 12:05:40 3

    I want to retrieve row 1), 2) 4) and 6) only

    i am using this query now

    select a.* from
    (select table.*, lag(date_and_time,1,0) over (order by called_party) as t2,lag(called_party,1,0) over (order by called_party) as c2 from table where duration < 10 order by calling_no, called_party, date_and_time) a
    where a.called_party=c2
    and a.date_and_time between (to_char(to_date(t2,'dd-mon-yyyy hh:mi:ss am')+1/(24*60*60),'dd-mon-yyyy hh:mi:ss am')) and (to_char(to_date(t2,'dd-mon-yyyy hh:mi:ss am')+360/(24*60*60),'dd-mon-yyyy hh:mi:ss am'))

    the issue is that it is only retrieving only first call, i want that second call also.i.e from (1) and (2) it gives only (1)
  2. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    OK --

    There are several problems. For one, your sample data shows dates in YYYY-MM-DD format, but your SQL is using a TO_DATE with DD-MON-YYYY format. Your sample data should match your sample query.

    Second, unformatted SQL is a pain to read. Take a few minutes to clean up your SQL when asking people for help.

    Third, you are indicating two different results that you want. First you indicate "I want to retrieve row 1), 2) 4) and 6) only". Then later you indicate "...i want that second call also.i.e from (1) and (2) it gives only (1)". Therefore, it's not clear if you're expecting two rows or four in your result set.

    Fourth -- assuming that the second result is the one you want (i.e. the rows marked 1 & 2), for this to happen, the call following 2 would need to have occurred within three minutes of call 2 based on your condition "...between an interval of 3 mins...". However, call 2 occurred on March 1st and call 3 on March 3rd. The interval between the calls is obviously greater than three minutes.

    Fifth -- while you indicate the interval should be three minutes, your date logic is using an interval of 360 seconds (six minutes). Regardless, call number 3 is still outside that range from call 2.

    Using the somewhat cleaner query below, I also return no row other than number 1 from your sample data:

    Code (Text):
    CREATE TABLE lag_test (
    clng_num         VARCHAR2(20),
    cled_num         VARCHAR2(20),
    date_time        VARCHAR2(40),
    call_duration    NUMBER);

    INSERT INTO lag_test VALUES ('9XXXXXXXX','9YYYYYYYY','2015-03-01 02:02:05', 8);
    INSERT INTO lag_test VALUES ('9XXXXXXXX','9YYYYYYYY','2015-03-01 02:03:00', 4);
    INSERT INTO lag_test VALUES ('9XXXXXXXX','9YYYYYYYY','2015-03-03 12:03:00', 2);
    INSERT INTO lag_test VALUES ('9XXXXYYYY','9YYYYYYXX','2015-03-01 12:03:00', 4);
    INSERT INTO lag_test VALUES ('9XXXXXXXX','9YYYYYYYY','2015-03-01 12:03:45', 45);
    INSERT INTO lag_test VALUES ('9XXXXXYYY','9YYYYYYXX','2015-03-01 12:05:40', 3);

    SELECT lagged.*
    FROM   (SELECT lt.*,
                   LAG(date_time,1,0) OVER (ORDER BY clng_num, cled_num, TO_DATE(date_time,'YYYY-MM-DD HH:MI:SS AM')) AS t2,
                   LAG(cled_num,1,0) OVER (ORDER BY clng_num, cled_num, TO_DATE(date_time,'YYYY-MM-DD HH:MI:SS AM')) AS c2
            FROM   lag_test lt
            WHERE  call_duration < 10
            ORDER BY clng_num, cled_num, TO_DATE(date_time,'YYYY-MM-DD HH:MI:SS AM')) lagged
    WHERE   lagged.cled_num = lagged.c2
    AND     TO_DATE(lagged.date_time,'YYYY-MM-DD HH:MI:SS AM') - TO_DATE(lagged.t2,'YYYY-MM-DD HH:MI:SS AM') BETWEEN 0 AND 3/1440;