Time Interval Overlaps - Teradata

I need help with interval overplaps. I have these records in one table (and much more): Example 1: Id---------StartDate------EndDate 794122 2011-05-10 2999-12-31 794122

Solution 1:

The result for example #4 doesn't match your data, shouldn't this be 9999, 2999-01-02 instead of 3000-01-01?

A typical solution for combining overlapping periods uses nested OLAP-functions, for your specific requirement (only the latest period) it can be a bit simplified to:

SELECT*FROM
 (
   SELECTDISTINCT-- DISTINCT is not neccessary, but results in a better plan
      Id,
      StartDate,
      MAX(EndDate) 
      OVER (PARTITIONBY Id) +1AS EndDate
   FROM dropme AS t
   QUALIFY -- find the gapCOALESCE(StartDate 
               -MAX(EndDate) 
                 OVER (PARTITIONBY Id
                       ORDERBY StartDate, EndDate
                       ROWSBETWEEN UNBOUNDED PRECEDING AND1 PRECEDING), 1) >0
 ) AS dt
QUALIFY 
   ROW_NUMBER() 
   OVER (PARTITIONBY Id
         ORDERBY StartDate DESC) =1
;

Solution 2:

You want the end date to be the first day of the following year?

select id, min(startdate) start_date, 
       cast(max(extract(yearfrom enddate)) +1||'-01-01'asdate) end_date
from table1
groupby id

Solution 3:

Are you just trying to do this?

select id, min(start_date) as start_date, max(end_date) as end_date
from t
group by id;

EDIT:

Now that I understand what you need. It identifies the rows that start a new period (using the not exists clause to look for overlaps). It then chooses the maximum start_date among those rows for each id:

select t.id, min(t.start_date) as start_date, max(t.end_date) as end_date
from (select id, max(start_date) as maxsd
      from t
      wherenotexists (select1from t t2
                        where t2.start_date < t.start_date and
                              t2.end_date >= t.start_date
                       )
      groupby id
     ) ids join
     t
     on t.id = ids.id and
        t.start_date >= maxsd
groupby t.id;

The final step joins back to the original data and does the aggregation on anything that starts after the start date.