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
mahjagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mahjagAuthor Commented:
I tried self join it did not work - may be analytical query will work - but I am not good at analytical query
0
gatorvipCommented:
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

0
mahjagAuthor Commented:
Thanks gatorvip

above query selected A type as N eventhough the dates are continous - is that correct ?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gatorvipCommented:
With what data? I tested it against the sample provided  and the result was "N"
0
mahjagAuthor Commented:
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
0
gatorvipCommented:
Change the column name from "Continous" to "Overlaps" or something - if there's an overlap between the next (or previous) period and the current one, the flag will be "Y" (like for B data set). If there's no overlap, then the flag is "N" (like the A data set).

ID                     VALUE                     DATE_START                DATE_END                  PREV_DT_START             NEXT_DT_START             OVERLAPS 
---------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ---------- 
1                      ATYPE                     01-JAN-11                 15-JAN-11                                           16-JAN-11                 N          
1                      ATYPE                     16-JAN-11                 31-JAN-11                 01-JAN-11                                           N          
2                      BTYPE                     01-JAN-11                 15-JAN-11                                           01-JAN-11                 Y          
2                      BTYPE                     01-JAN-11                 15-JAN-11                 01-JAN-11                                           Y

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.