Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query
icon3.gif  Sql Query [message #247869] Wed, 27 June 2007 07:16 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi,
I have a table named Table1 with following details.

specialist_id number(5)
account_no number(10)
date_created date


Data
Specialist ID Account No Date Created
1 100 15-SEP-2002 09:10:10
1 100 15-SEP-2002 09:11:10
1 100 15-SEP-2002 10:10:10
1 100 15-MAR-2001 09:10:10
1 100 15-JUN-2002 09:11:10
1 400 15-JUN-2002 09:11:11
2 100 15-SEP-2002 09:12:10
2 200 14-SEP-2002 10:10:10
2 200 14-SEP-2002 10:11:10
2 200 14-SEP-2002 19:10:10
2 200 14-SEP-2001 10:10:10
2 200 14-MAR-2001 19:10:10
2 500 15-JUN-2002 09:11:11

I need a listing of duplicate interaction within a difference
of 5 minutes for a given speciliast for the same account number
between a date range 10-SEP-2002 and 16-SEP-2002.

My output for the query should be
Specialist ID Account No Date Created
1 100 15-SEP-2002 09:10:10
1 100 15-SEP-2002 09:11:10
2 200 14-SEP-2002 10:10:10
2 200 14-SEP-2002 10:11:10

Is it possible through a single query or inline views?
Specialist ID number 1 is working on the same account number within
5 minutes difference of date created field. Same is for specialist 2 for account number 200.
Your help is appreciated.Please attach with query
Re: Sql Query [message #247881 is a reply to message #247869] Wed, 27 June 2007 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Post a test case: create table and insert statements.

Regards
Michel

[Updated on: Wed, 27 June 2007 07:41]

Report message to a moderator

Re: Sql Query [message #247901 is a reply to message #247881] Wed, 27 June 2007 08:14 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i try to write query…I took third field as number instead of "Date Created

select * from tmp1
1,100,1
1,100,2
1,100,6
1,100,11
1,100,13


i tried some thing like that..

select id,account,d1 from (select id, account ,d1,lead(d1) over(order by id) nextd1 from tmp1) where nextd1-d1 <5 

1,100,1
1,100,2
1,100,11



You can try some thing like

--Yash

Previous Topic: Sql Query
Next Topic: Avoiding Full table scan
Goto Forum:
  


Current Time: Sat Dec 03 18:19:20 CST 2016

Total time taken to generate the page: 0.09273 seconds