asked on

identify date overlaps

I need to find records in a table that has date start and date end that are not continous everything else is same value- here is an example
Table 1
Record 1
ID Value Date start date end
1 A Type 01-JAN-11 15-JAN-11
1 A Type 16-JAN-11 31-JAN-11
2 Btype 01-JAN-11 15-JAN-11
2 Btype 01-JAN-11 15-JAN-11
I have to write a query to eliminate those records in Table if they are continous - above data I want to select A type and not B type

mahjag

ASKER

I tried self join it did not work - may be analytical query will work - but I am not good at analytical query

gatorvip

How about something like this (where you may need to adapt depending on your data)

select t2.*
  , (case when prev_dt_start < date_start or next_dt_start > date_end then 'N' else 'Y' end) continuous  
from
(
select t1.*
  ,lag(date_start) over(partition by id, value order by  date_start) prev_dt_start
  ,lead(date_start) over(partition by id, value order by  date_start) next_dt_start
from <your_table> t1
) t2

Open in new window

mahjag

ASKER

Thanks gatorvip

above query selected A type as N eventhough the dates are continous - is that correct ?

gatorvip

With what data? I tested it against the sample provided and the result was "N"

mahjag

ASKER

I used the same sample data - I want to understand N means continous or not - that part I got confused- I thought Y means continous and N is not - which means from above test data A Type should be selected and not B

ASKER CERTIFIED SOLUTION

gatorvip

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial