Find record "in the middle" of another record 1

[Sandman83]

Hello All,

I have a table with start times and end times. I need to find every record that has another record in the table where the start time is greater than the start time and less than the end time of the first record.

For ex.

Rec StartTime EndTime
01 12:55:06 12:59:52
02 13:03:45 13:05:10
03 13:01:16 13:06:14
04 13:10:21 13:12:02
05 13:11:01 13:14:20

I would want to find record 2 & record 5. Thanks for any suggestions.

 

[lewisp]

I dont follow your logic. Do you mean record 5 only would be chosen because the start time is between the start and end of the previous record.

The start time of record 2 is not between the start and end of record 1, and the start of 3 is less than the start of 2....

 

[Sandman83]

Thanks for the reply, let me try and clarify.

The start time just needs to be between the start and end time for any other record. Record 2 would be selected because the start time is between the start and end time of record 3, and record 5 would be selected because the start time is between the start and end time of record 4.

It is also possible that there would be multiple records that would have a start time between the start time and end time of one record.

 

[sem]

select * from yourTab a
where exists 
  (select 1 from yourTab b
   where a.startTime between b.startTime and b.endTime)

Regards, Dima

http://seminihin.narod.ru

 

[Sandman83]

Thanks Sem. I will be out of town until Monday. I will try it then and let you know.

 

[SantaMufasa]

Tim,

Could you please do the following:

1) Add another row that would produce the scenario you mention of having multiple rows that fall between another row's start and end times.

2) Show us how you want the output to appear for your now-6 rows of input data.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Sandman83]

Thanks Sem, works great!!