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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mahjagAuthor Commented:
Thanks gatorvip

above query selected A type as N eventhough the dates are continous - is that correct ?
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.