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>