OraFAQ Forum: SQL & PL/SQL » Data selection on basis of columns from two consecutive rows (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Data selection on basis of columns from two consecutive rows (merged) (11g oracle database , window os)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data selection on basis of columns from two consecutive rows (merged) [message #639275]

Sat, 04 July 2015 04:10

pankajmaurya55
Messages: 2
Registered: July 2015
Location: India

Junior Member

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

Report message to a moderator

Re: Data selection on basis of columns from two consecutive rows [message #639276 is a reply to message #639275]

Sat, 04 July 2015 04:22

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags[/quote]

Report message to a moderator

Re: Data selection on basis of columns from two consecutive rows [message #639277 is a reply to message #639275]

Sat, 04 July 2015 04:26

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

pankajmaurya55 wrote on Sat, 04 July 2015 14:40

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.

All filter conditions are simple. For the previous call interval use LAG() analytic function.

Report message to a moderator

Re: Data selection on basis of columns from two consecutive rows [message #639279 is a reply to message #639277]

Sat, 04 July 2015 04:32

pankajmaurya55
Messages: 2
Registered: July 2015
Location: India

Junior Member

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)

[Updated on: Sat, 04 July 2015 04:34]

Report message to a moderator

oracle query [message #639280 is a reply to message #639275]

Sat, 04 July 2015 04:35

anuradha.mudgal@.com
Messages: 1
Registered: July 2015
Location: new delhi

Junior Member

hello, i have table which contains data like as given below
calling_no called_party duration date_and_time
999999999 8888888888 10 13-MAR-2015 04:40:04 PM
999999999 8888888888 7 13-MAR-2015 04:46:04 PM
999999999 8888888882 19 13-MAR-2015 04:40:04 PM
999999999 8888888883 0 13-MAR-2015 04:41:04 PM
999999992 9888888888 10 13-MAR-2015 04:40:04 PM
999999992 9888888888 7 13-MAR-2015 04:46:04 PM
999999992 9888888888 19 13-MAR-2015 04:50:04 PM
999999999 8888888888 0 13-MAR-2015 04:41:04 PM

i want my data like this

calling_no called_party duration date_and_time
999999999 8888888888 10 13-MAR-2015 04:40:04 PM
999999999 8888888888 7 13-MAR-2015 04:46:04 PM
999999992 9888888888 10 13-MAR-2015 04:40:04 PM
999999992 9888888888 7 13-MAR-2015 04:46:04 PM
999999992 9888888888 19 13-MAR-2015 04:50:04 PM

When duration is 0 , 7, 10, 19 Seconds where the repeat call on same number was made within 360 seconds.

kindly make me reply as soon as possible thanks in advance for auspicious help.

Report message to a moderator

Re: oracle query [message #639281 is a reply to message #639280]

Sat, 04 July 2015 04:40

John Watson
Messages: 9003
Registered: January 2010
Location: Global Village

Senior Member

You could ask your colleague, Pankaj, for help.

http://www.orafaq.com/forum/t/197790/0/

[Updated on: Sat, 04 July 2015 04:41]

Report message to a moderator

Re: Data selection on basis of columns from two consecutive rows [message #639282 is a reply to message #639279]

Sat, 04 July 2015 04:49

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: oracle query [message #639283 is a reply to message #639280]

Sat, 04 July 2015 04:50

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And follow the same rules:
Read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Note that we don't do homework.

Report message to a moderator

Re: oracle query [message #639284 is a reply to message #639283]

Sat, 04 July 2015 04:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, what is the purpose of posting with two different handles to ask the same question?

Report message to a moderator

Previous Topic:	SUM CASE date difference calculation
Next Topic:	SQL Where Limitation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 27 15:26:01 CDT 2026