Teradata ROWS UNBOUNDED PRECEDING

0
votes

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
2
teradata
Of course rows unbounded preceding includes the current row, it's a shortcut for rows between unbounded preceding and current row. Can you show what exact result should be returned? – dnoeth
are you sure ID103 goes to 2016 and not 2001? – Beth

2 Answers

1
votes

There are a couple of ways to go about this. Since date ranges overlapping can get tricky, I would use Teradata's Period logic and a self join:

SELECT
    *
FROM
    table t1
    INNER JOIN table t2 ON
        period(t1.start_date, next(t1.end_date)) P_INTERSECT period(t2.start_date, next(t2.end_date)) IS NOT NULL

That will convert your start and end dates to a PERIOD data type and then look for records that have intersecting periods. The results will be the two records, combined into a single record, where the overlap takes place.

0
votes

You can also try it the old-fashioned way, like:

CREATE TABLE db.t 
  (id INT,
  start_date DATE,
  end_date DATE);
INSERT INTO db.t VALUES (101,'2001-01-01','2001-01-31');
INSERT INTO db.t VALUES (102,'2001-02-01','2001-02-28');
INSERT INTO db.t VALUES (103,'2001-02-26','2001-03-31');
INSERT INTO db.t VALUES (104,'2001-03-15','2001-04-30');
INSERT INTO db.t VALUES (105,'2002-05-01','2002-05-31');
INSERT INTO db.t VALUES (106,'2002-12-05','2002-12-31');
INSERT INTO db.t VALUES (107,'2002-12-01','2003-01-05');
SELECT 
  t.id,
  t.start_date,
  t.end_date,
  MAX(CASE WHEN o.id IS NULL THEN 'non-overlapping'
  ELSE 'overlaps with' || o.id END) AS flag
FROM
  db.t t LEFT OUTER JOIN
  db.t o ON
  t.start_date < o.end_date AND
  t.end_date >= o.start_date AND
  t.id <> o.id
GROUP BY 1,2,3

returns (sorted manually)

id  start_date  end_date    flag
101 01/01/2001  01/31/2001  non-overlapping
102 02/01/2001  02/28/2001  overlaps with        103
103 02/26/2001  03/31/2001  overlaps with        104
104 03/15/2001  04/30/2001  overlaps with        103
105 05/01/2002  05/31/2002  non-overlapping
106 12/05/2002  12/31/2002  overlaps with        107
107 12/01/2002  01/05/2003  overlaps with        106