Eliminate records based on Overlap datetime

[rusa54]

Hi,

I am trying to filter out records based on time overlap. For example I have records ordered by type and date. If there is an overlap.. the record that has a start date earlier than the second record should be eliminated. I have a sample data below

TYPE     START DATE          END DATE
A     02/28/10 13:00:00  02/28/10  14:00:00
A     03/01/10 10:00:00  03/01/10  15:00:00
A     03/01/10 12:00:00  03/01/10  20:00:00
B     02/15/10 06:00:00  02/17/10  09:00:00
B     02/16/10 07:00:00  02/17/10  09:00:00

Result should be

TYPE     START DATE          END DATE
A     02/28/10 13:00:00  02/28/10  14:00:00
A     03/01/10 12:00:00  03/01/10  20:00:00
B     02/16/10 07:00:00  02/17/10  09:00:00

I hope it is making sense. Thanks a lot in advance!

 

[jaxtell]

This seems to work for me with your sample data

select type_code, start_date, end_date from (select jt.*, lag(start_date,1) over (partition by type_code order by end_date desc, start_date desc) lsd, lag(end_date,1) over (partition by type_code order by end_date desc, start_date desc) led from test_table jt) where nvl(led,end_date) >= end_date and nvl(lsd,end_date) >= end_date order by type_code, start_date

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/