Teradata ROWS UNBOUNDED PRECEDING

Database
Tourist

Teradata ROWS UNBOUNDED PRECEDING

I have below data set with me. I want to ensure that for any particular ID start_date, end_date and the period in between these two dates are not overlapping with dates of any other ID.

ID Start_Date End_Date

101 01-01-2001 31-01-2001

102 01-02-2001 28-02-2001

103 26-02-2001 31-03-2016

104 15-03-2001 30-04-2001

105 01-05-2002 31-05-2002

106 05-12-2002 31-12-2002

107 15-12-2002 05-01-2003

To do this I created below query:

select id,start_date,end_date,

case
when end_date< max(end_date) over(order by start_date rows unbounded preceding)

then 'overlapping'

when start_date<max(end_date) over(order by start_date rows unbounded preceding)

then 'overlapping'

else 'non-overlapping'
end as FLAG from table

I am getting below output having all flag as 'overlapping' which is not correct. I think 'rows unbounded preceding' is taking current row also in calculation:
Can you please let me know where I am wrong:

ID Start_Date End_Date Flag

101 01-01-2001 31-01-2001 Overlapping

102 01-02-2001 28-02-2001 Overlapping

103 26-02-2001 31-03-2016 Overlapping

104 15-03-2001 30-04-2001 Overlapping

105 01-05-2002 31-05-2002 Overlapping

106 05-12-2002 31-12-2002 Overlapping

107 15-12-2002 05-01-2003 Overlapping

1 REPLY

Re: Teradata ROWS UNBOUNDED PRECEDING

SyntaxEditor Code Snippet

SyntaxEditor Code Snippet
create multiset table t 
(
    ID          int   ,Start_Date  date   ,End_Date    date)
    unique primary index (ID);

insert into t (Id,Start_Date,End_Date) values (101,date '2001-01-01', date '2001-01-31');
insert into t (Id,Start_Date,End_Date) values (102,date '2001-02-01', date '2001-02-28');
insert into t (Id,Start_Date,End_Date) values (103,date '2001-02-26', date '2016-03-31');
insert into t (Id,Start_Date,End_Date) values (104,date '2001-03-15', date '2001-04-30');
insert into t (Id,Start_Date,End_Date) values (105,date '2002-05-01', date '2002-05-31');
insert into t (Id,Start_Date,End_Date) values (106,date '2002-12-05', date '2002-12-31');
insert into t (Id,Start_Date,End_Date) values (107,date '2002-12-15', date '2003-01-05');


-- Educational version
select      ID           ,min (Start_Date)    over (order by Start_Date,End_Date rows between 1 preceding and 1 preceding)    as preve_Start_Date           ,min (End_Date)      over (order by Start_Date,End_Date rows between 1 preceding and 1 preceding)    as prev_End_Date           
           ,Start_Date           ,End_Date           
           ,case 
                when Start_Date = prev_End_Date then 'Meet'
                when Start_Date < prev_End_Date then 'Overlap'
            end                                                         as flag
           
from        t

order by    Start_Date           ,End_date
;