Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Deepthi.

Asked: January 29, 2017 - 4:11 pm UTC

Last updated: February 06, 2017 - 2:47 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Hi Tom, Could you please help me with the SQL in finding if the record is a overlap or not.
Here is the test case..
seq_val is calculated first and would like to check overlaps based on that order..
ID Start_dt end_dt Seq_val
1 01/01/2005 12/31/2006 2
1 01/06/2005 05/31/2005 1
1 06/01/2005 05/31/2006 3
1 06/01/2006 09/19/2006 4
1 09/20/2006 05/31/2009 6
1 09/20/2006 05/31/2007 5
1 06/01/2007 05/31/2008 7
1 06/01/2008 05/31/2009 8

and expected output should be...

ID Start_dt end_dt Seq_val Overlap_flag
1 01/01/2005 12/31/2006 2 N
1 01/06/2005 05/31/2005 1 Y
1 06/01/2005 05/31/2006 3 Y
1 06/01/2006 09/19/2006 4 Y
1 09/20/2006 05/31/2009 6 N
1 09/20/2006 05/31/2007 5 Y
1 06/01/2007 05/31/2008 7 Y
1 06/01/2008 05/31/2009 8 Y

Thanks a lot for your help in advance...

with LiveSQL Test Case:

and Connor said...

Couple of things

1) you need to save your test case in livesql as a *script* which gives you a link. You've just given us a link to your session

2) what does 'seq_val is calculated first and would like to check overlaps based on that order.. ' mean?

So with (2) in mind - here's something that might get you started, or at least show you what I have in mind.


SQL> create table t ( id int, df date, dt date, seq int );

Table created.

SQL> alter session set nls_date_format = 'mm/dd/yyyy';

Session altered.

SQL>
SQL> insert into t values (1 ,'01/01/2005', '12/31/2006', 2);

1 row created.

SQL> insert into t values (1 ,'01/06/2005', '05/31/2005', 1 );

1 row created.

SQL> insert into t values (1 ,'06/01/2005', '05/31/2006', 3 );

1 row created.

SQL> insert into t values (1 ,'06/01/2006', '09/19/2006', 4 );

1 row created.

SQL> insert into t values (1 ,'09/20/2006', '05/31/2009', 6 );

1 row created.

SQL> insert into t values (1 ,'09/20/2006', '05/31/2007', 5 );

1 row created.

SQL> insert into t values (1 ,'06/01/2007', '05/31/2008', 7 );

1 row created.

SQL> insert into t values (1 ,'06/01/2008', '05/31/2009', 8 );

1 row created.

SQL>
SQL> select t.*
  2  from t;

        ID DF         DT                SEQ
---------- ---------- ---------- ----------
         1 01/01/2005 12/31/2006          2
         1 01/06/2005 05/31/2005          1
         1 06/01/2005 05/31/2006          3
         1 06/01/2006 09/19/2006          4
         1 09/20/2006 05/31/2009          6
         1 09/20/2006 05/31/2007          5
         1 06/01/2007 05/31/2008          7
         1 06/01/2008 05/31/2009          8

8 rows selected.

SQL>
SQL> select t.* ,
  2    ( select count(*)
  3      from   t t_inner
  4      where  t_inner.seq < t.seq and
  5             ( t_inner.df between t.df and t.dt
  6            or   t_inner.dt between t.df and t.dt
  7            or ( t_inner.df < t.df and t_inner.dt > t.dt )
  8            )
  9     ) overlapper
 10  from t;

        ID DF         DT                SEQ OVERLAPPER
---------- ---------- ---------- ---------- ----------
         1 01/01/2005 12/31/2006          2          1
         1 01/06/2005 05/31/2005          1          0
         1 06/01/2005 05/31/2006          3          1
         1 06/01/2006 09/19/2006          4          1
         1 09/20/2006 05/31/2009          6          2
         1 09/20/2006 05/31/2007          5          1
         1 06/01/2007 05/31/2008          7          1
         1 06/01/2008 05/31/2009          8          1

8 rows selected.

SQL>
SQL>


Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Deepthi Nar, January 29, 2017 - 8:54 pm UTC

Thanks a lot for the respose.Actually I have to calculate the flag i.e Y(if overlap) and N if its not overlap.
Could you please help me with that...
Connor McDonald
January 30, 2017 - 8:11 pm UTC

SQL> select count(*),
  2     case when count(*) = 1 then 'Y' else 'N' end easy_peasy
  3  from dual;

  COUNT(*) E
---------- -
         1 Y

1 row selected.

SQL> select count(*),
  2     case when count(*) = 1 then 'Y' else 'N' end easy_peasy
  3  from dual
  4  where 1=0;

  COUNT(*) E
---------- -
         0 N

1 row selected.


MODEL clause

Rajeshwaran, Jeyabal, January 30, 2017 - 7:17 am UTC

drop table t purge;
create table t(
 id int,
 start_dt date,
 end_dt date,
 seq_val int );
insert into t values( 1, to_date('01/01/2005','mm/dd/yyyy'), to_date('12/31/2006','mm/dd/yyyy'),2 );
insert into t values( 1, to_date('01/06/2005','mm/dd/yyyy'), to_date('05/31/2005','mm/dd/yyyy'),1 );
insert into t values( 1, to_date('06/01/2005','mm/dd/yyyy'), to_date('05/31/2006','mm/dd/yyyy'),3 );
insert into t values( 1, to_date('06/01/2006','mm/dd/yyyy'), to_date('09/19/2006','mm/dd/yyyy'),4 );
insert into t values( 1, to_date('09/20/2006','mm/dd/yyyy'), to_date('05/31/2009','mm/dd/yyyy'),6 );
insert into t values( 1, to_date('09/20/2006','mm/dd/yyyy'), to_date('05/31/2007','mm/dd/yyyy'),5 );
insert into t values( 1, to_date('06/01/2007','mm/dd/yyyy'), to_date('05/31/2008','mm/dd/yyyy'),7 );
insert into t values( 1, to_date('06/01/2008','mm/dd/yyyy'), to_date('05/31/2009','mm/dd/yyyy'),8 );
commit;

demo@ORA11G> select *
  2  from t
  3  model
  4    dimension by (
  5      row_number() over(order by start_dt) rn )
  6    measures( id, start_dt, end_dt, seq_val ,cast(null as varchar2(1)) as flag )
  7    rules(
  8      flag[any] order by rn = case
  9                      when cv(rn)=1 then 'N'
 10                      when start_dt[cv()] between min(start_dt) [rn<cv(rn)]
 11                              and max(end_dt) [rn<cv(rn)]
 12                        and end_dt[cv()] between min(start_dt) [rn<cv(rn)]
 13                              and max(end_dt) [rn<cv(rn)]
 14                        then 'Y'
 15                        else 'N' end )
 16  /

        RN         ID START_DT  END_DT       SEQ_VAL F
---------- ---------- --------- --------- ---------- -
         1          1 01-JAN-05 31-DEC-06          2 N
         2          1 06-JAN-05 31-MAY-05          1 Y
         3          1 01-JUN-05 31-MAY-06          3 Y
         4          1 01-JUN-06 19-SEP-06          4 Y
         5          1 20-SEP-06 31-MAY-09          6 N
         6          1 20-SEP-06 31-MAY-07          5 Y
         7          1 01-JUN-07 31-MAY-08          7 Y
         8          1 01-JUN-08 31-MAY-09          8 Y

8 rows selected.

demo@ORA11G>

with Analytics

Rajeshwaran, Jeyabal, January 30, 2017 - 7:23 am UTC

Analytics Rock.
Analytics Roll.

demo@ORA11G> select id, start_dt, end_dt, seq_val, rn ,
  2         case when rn =1 then 'N'
  3              when start_dt between min_dt and max_dt and
  4                   end_dt between min_dt and max_dt
  5                   then 'Y' else 'N' end flag
  6  from (
  7  select t.* ,
  8    row_number() over( order by start_dt ) rn ,
  9    min(start_dt) over( order by start_dt
 10          rows between unbounded preceding
 11          and 1 preceding ) as min_dt,
 12    max(end_dt) over( order by start_dt
 13          rows between unbounded preceding
 14          and 1 preceding ) as max_dt
 15  from t
 16       )
 17  order by start_dt
 18  /

        ID START_DT  END_DT       SEQ_VAL         RN F
---------- --------- --------- ---------- ---------- -
         1 01-JAN-05 31-DEC-06          2          1 N
         1 06-JAN-05 31-MAY-05          1          2 Y
         1 01-JUN-05 31-MAY-06          3          3 Y
         1 01-JUN-06 19-SEP-06          4          4 Y
         1 20-SEP-06 31-MAY-09          6          5 N
         1 20-SEP-06 31-MAY-07          5          6 Y
         1 01-JUN-07 31-MAY-08          7          7 Y
         1 01-JUN-08 31-MAY-09          8          8 Y

8 rows selected.

demo@ORA11G>

play around

Praveen Ray, January 30, 2017 - 9:31 am UTC

select t2.*, case when t1.id is null then 'N' else 'Y' end flag
from t t1 right join t t2
on (t1.rowid <> t2.rowid and t1.start_dt <= t2.start_dt and t1.end_dt >= t2.end_dt);

Deepthi Nar, January 30, 2017 - 2:15 pm UTC

There is a small Correction in my requirement.

ID START_DT END_DT SEQ_VAL RN F
---------- --------- --------- ---------- ---------- -
1 01-JAN-05 31-DEC-06 2 1 N
1 06-JAN-05 31-MAY-05 1 2 Y
1 01-JUN-05 31-MAY-06 3 3 Y
1 01-JUN-06 19-SEP-06 4 4 Y
1 20-SEP-06 31-MAY-09 6 5 N (This should be Y because its an overlap with first record.I have to compare my end date value for the flag=N)
1 20-SEP-06 31-MAY-07 5 6 Y
1 01-JUN-07 31-MAY-08 7 7 N
1 01-JUN-08 31-MAY-09 8 8 N

To Deepthi

Rajeshwaran, Jeyabal, January 30, 2017 - 5:37 pm UTC

Deepthi,

Start with this inputs, and explain your requirements and help us to understand how did you arrive Y or N flag for each row.

without that we would be guessing and will be going back and forth with this.

demo@ORA11G> select * from t order by start_dt;

        ID START_DT    END_DT         SEQ_VAL
---------- ----------- ----------- ----------
         1 01-JAN-2005 31-DEC-2006          2
         1 06-JAN-2005 31-MAY-2005          1
         1 01-JUN-2005 31-MAY-2006          3
         1 01-JUN-2006 19-SEP-2006          4
         1 20-SEP-2006 31-MAY-2009          6
         1 20-SEP-2006 31-MAY-2007          5
         1 01-JUN-2007 31-MAY-2008          7
         1 01-JUN-2008 31-MAY-2009          8

8 rows selected.


Reread the above requirement, but still unclear. see this.
        ID START_DT    END_DT         SEQ_VAL
---------- ----------- ----------- ----------
         1 01-JAN-2005 31-DEC-2006          2 N
         1 06-JAN-2005 31-MAY-2005          1 Y
         1 01-JUN-2005 31-MAY-2006          3 Y
         1 01-JUN-2006 19-SEP-2006          4 Y
         1 20-SEP-2006 31-MAY-2009          6 N ==> (This should be Y because its an overlap with first record.I have to compare my end date value for the flag=N) 
         1 20-SEP-2006 31-MAY-2007          5 Y
         1 01-JUN-2007 31-MAY-2008          7 N ==> Why not this should  be Y, because its overlap with the fifth record in this result set?
         1 01-JUN-2008 31-MAY-2009          8 N

Deepthi Nar, January 30, 2017 - 6:01 pm UTC

ID START_DT END_DT SEQ_VAL
---------- ----------- ----------- ----------
1 01-JAN-2005 31-DEC-2006 2 N
1 06-JAN-2005 31-MAY-2005 1 Y
1 01-JUN-2005 31-MAY-2006 3 Y
1 01-JUN-2006 19-SEP-2006 4 Y
1 20-SEP-2006 31-MAY-2009 6 N ==> (This should be Y because its an overlap with first record.I have to compare my end date value for the flag=N)
1 20-SEP-2006 31-MAY-2007 5 Y
1 01-JUN-2007 31-MAY-2008 7 N ==> Why not this should be Y, because its overlap with the fifth record in this result set?
1 01-JUN-2008 31-MAY-2009 8 N


Yes that record should also be Y

ID START_DT END_DT SEQ_VAL
---------- ----------- ----------- ----------
1 01-JAN-2005 31-DEC-2006 2 N
1 06-JAN-2005 31-MAY-2005 1 Y
1 01-JUN-2005 31-MAY-2006 3 Y
1 01-JUN-2006 19-SEP-2006 4 Y
1 20-SEP-2006 31-MAY-2009 6 N ==> (This should be Y because its an overlap with first record.I have to compare my end date value for the flag=N)
1 20-SEP-2006 31-MAY-2007 5 Y
1 01-JUN-2007 31-MAY-2008 7 Y ==> Updated to Y 1 01-JUN-2008 31-MAY-2009 8 N

Missing Requirements

Rajeshwaran, Jeyabal, January 31, 2017 - 1:55 am UTC

....
Start with this inputs, and explain your requirements and help us to understand how did you arrive Y or N flag for each row.
....


missing requirements, can't guess more than this. Hope the inline comments help to understand the Flag values.

demo@ORA11G> select id, start_dt, end_dt, seq_val, rn ,
  2            case when rn =1 then 'N'
  3                 when (start_dt between min_dt and max_dt) OR
  4                      (end_dt between min_dt and max_dt)
  5                      then 'Y' else 'N' end flag
  6     from (
  7     select t.* ,
  8       row_number() over( order by start_dt ) rn ,
  9       min(start_dt) over( order by start_dt
 10             rows between unbounded preceding
 11             and 1 preceding ) as min_dt,
 12       max(end_dt) over( order by start_dt
 13             rows between unbounded preceding
 14             and 1 preceding ) as max_dt
 15     from t
 16          )
 17  order by start_dt;

        ID START_DT    END_DT         SEQ_VAL         RN F
---------- ----------- ----------- ---------- ---------- -
         1 01-JAN-2005 31-DEC-2006          2          1 N ==> Since it is the first row set to N
         1 06-JAN-2005 31-MAY-2005          1          2 Y ==> Overlap with first row set to Y
         1 01-JUN-2005 31-MAY-2006          3          3 Y ==> Overlap with first row set to Y
         1 01-JUN-2006 19-SEP-2006          4          4 Y ==> Overlap with first row set to Y
         1 20-SEP-2006 31-MAY-2009          6          5 Y ==> Start_dt Overlap with first row set to Y
         1 20-SEP-2006 31-MAY-2007          5          6 Y ==> Overlap with fifth row set to Y
         1 01-JUN-2007 31-MAY-2008          7          7 Y ==> Overlap with fifth row set to Y
         1 01-JUN-2008 31-MAY-2009          8          8 Y ==> Overlap with fifth row set to Y

8 rows selected.

demo@ORA11G>


If this doesn't matchup with your expected outcomes, then start with an input, explain us the logic (spell the requirements) how to transform the inputs to the expected outputs.

Deepthi Nar, January 31, 2017 - 3:20 pm UTC

Hi,

Sorry for the coufusion...I am explaining the requirement more clearly::

ID START_DT END_DT SEQ_VAL RN F
---------- ----------- ----------- ---------- ---------- -
1 01-JAN-2005 31-DEC-2006 2 1 N ==> Since it is the first row set to N
1 06-JAN-2005 31-MAY-2005 1 2 Y ==> Overlap with first row set to Y
1 01-JUN-2005 31-MAY-2006 3 3 Y ==> Overlap with first row set to Y
1 01-JUN-2006 19-SEP-2006 4 4 Y ==> Overlap with first row set to Y
1 20-SEP-2006 31-MAY-2009 6 5 Y ==> Start_dt Overlap with first row set to Y
1 20-SEP-2006 31-MAY-2007 5 6 Y ==> Overlap with First row set to Y
1 01-JUN-2007 31-MAY-2008 7 7 N ==> We cannot consider this as overlap because we do not have any record flagged as N with those dates
1 01-JUN-2008 31-MAY-2009 8 8 N ==> We cannot consider this as overlap because we do not have any record flagged as N with those dates

Chris Saxon
January 31, 2017 - 4:36 pm UTC

You mean something like this:

select start_dt, end_dt,
       case 
         when start_dt = fvst and end_dt = fvend 
           then 'N'
         when start_dt between fvst and fvend or 
              end_dt between fvst and fvend  
           then 'Y' 
           else 'N' 
       end flag
from   (
select t.*, first_value(start_dt) over (order by start_dt) fvst,
       first_value(end_dt) over (order by start_dt) fvend
from   t
);

START_DT     END_DT       FLAG  
01-Jan-2005  31-Dec-2006  N     
06-Jan-2005  31-May-2005  Y     
01-Jun-2005  31-May-2006  Y     
01-Jun-2006  19-Sep-2006  Y     
20-Sep-2006  31-May-2007  Y     
20-Sep-2006  31-May-2009  Y     
01-Jun-2007  31-May-2008  N     
01-Jun-2008  31-May-2009  N

Deepthi Nar, January 31, 2017 - 5:11 pm UTC

I Did not work as expected.

START_DT END_DT FLAG
01-Jan-2005 31-Dec-2006 N
06-Jan-2005 31-May-2005 Y
01-Jun-2005 31-May-2006 Y
01-Jun-2006 19-Sep-2006 Y
20-Sep-2006 31-May-2007 Y
20-Sep-2006 31-May-2009 Y
01-Jun-2007 31-May-2008 N
01-Jun-2008 31-May-2009 N
01-jun-2008 31-dec-2008 Y (If I have this record it should flag as Y because its an overlap with above record which has its flag as N.But the given SQL is flagging this record as N )
Chris Saxon
February 01, 2017 - 2:47 pm UTC

It gave the output you asked for in the previous review...

Be complete in your examples!

Inter-row calculations

Rajeshwaran, Jeyabal, February 01, 2017 - 6:05 am UTC

This seems to be an Excellent use case for inter-row calculations.

look n-rows back and see if the flag=N, if flag=N then those start_dt and end_dt values should be considered for overlap check validations.

Here is an approach using MODEL clause, don't see a way of doing this in Analytics.

demo@ORA11G> set feedback off
demo@ORA11G> drop table t purge;
demo@ORA11G> create table t(start_dt date,end_dt date);
demo@ORA11G> insert into t values( to_date('01-Jan-2005','dd-mon-yyyy') , to_date('31-Dec-2006','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('06-Jan-2005','dd-mon-yyyy') , to_date('31-May-2005','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('01-Jun-2005','dd-mon-yyyy') , to_date('31-May-2006','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('01-Jun-2006','dd-mon-yyyy') , to_date('19-Sep-2006','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('20-Sep-2006','dd-mon-yyyy') , to_date('31-May-2007','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('20-Sep-2006','dd-mon-yyyy') , to_date('31-May-2009','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('01-Jun-2007','dd-mon-yyyy') , to_date('31-May-2008','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('01-Jun-2008','dd-mon-yyyy') , to_date('31-May-2009','dd-mon-yyyy') );
demo@ORA11G> insert into t values( to_date('01-jun-2008','dd-mon-yyyy') , to_date('31-dec-2008','dd-mon-yyyy') );
demo@ORA11G> commit;
demo@ORA11G> set feedback on
demo@ORA11G> select * from t;

START_DT    END_DT
----------- -----------
01-JAN-2005 31-DEC-2006
06-JAN-2005 31-MAY-2005
01-JUN-2005 31-MAY-2006
01-JUN-2006 19-SEP-2006
20-SEP-2006 31-MAY-2007
20-SEP-2006 31-MAY-2009
01-JUN-2007 31-MAY-2008
01-JUN-2008 31-MAY-2009
01-JUN-2008 31-DEC-2008

9 rows selected.

demo@ORA11G>
demo@ORA11G> select *
  2  from t
  3  model
  4    dimension by ( row_number() over(order by start_dt) rn)
  5    measures( start_dt, end_dt, cast(null as varchar2(1)) flag)
  6    rules(
  7      flag[any] order by rn = case when cv(rn)=1 then 'N'
  8                          when ( start_dt[cv()] between min(decode(flag,'N',start_dt)) [rn<cv()]
  9                                      and max(decode(flag,'N',end_dt)) [rn<cv()] ) or
 10                              ( end_dt[cv()] between min(decode(flag,'N',start_dt)) [rn<cv()]
 11                                      and max(decode(flag,'N',end_dt)) [rn<cv()] )
 12                          then 'Y'
 13                          else 'N' end ) ;

        RN START_DT    END_DT      F
---------- ----------- ----------- -
         1 01-JAN-2005 31-DEC-2006 N
         2 06-JAN-2005 31-MAY-2005 Y
         3 01-JUN-2005 31-MAY-2006 Y
         4 01-JUN-2006 19-SEP-2006 Y
         5 20-SEP-2006 31-MAY-2007 Y
         6 20-SEP-2006 31-MAY-2009 Y
         7 01-JUN-2007 31-MAY-2008 N
         8 01-JUN-2008 31-MAY-2009 N
         9 01-JUN-2008 31-DEC-2008 Y

9 rows selected.

demo@ORA11G>


Chris Saxon
February 01, 2017 - 2:46 pm UTC

Nice work.

Deepthi Nar, February 05, 2017 - 8:37 pm UTC

Awesome it worked. Thanks a lot for the help.
Extension to this question....After calculating the flag I would like to see only the rows which were flagged either as Y or N?

How to add that filter to the query?

Thanks a lot for the help.....

Connor McDonald
February 06, 2017 - 2:47 pm UTC

You can just wrap it an inline view, ie

select *
from 
  ( your current query )
where col in ('Y','N')


Testing

Testing, March 23, 2017 - 2:20 pm UTC