Simple merging of records based on Date

Database

Simple merging of records based on Date

Hi

I am new to Teradata.I have provided my input and desire output table structure.Kindly help to write a simple query tp achieve the output

Eno      Name        strt_date           end_date

1         ABC           03-15-2001        07-19-2001

2         ABC           07-20-2001        09-22-2001

3         ABC          09-23-2001         12-31-9999

Thus the output i require is:

Eno      Name        strt_date           end_date

1         ABC           03-15-2001        12-31-9999

Thus i want to combine the data into one record when the dates are continuous with no gaps as shown above.

Thanks,

Drake

3 REPLIES
Enthusiast

Re: Simple merging of records based on Date

Try this -

Select min(eno), name, min (strt_dt) , max(end_dt) from emptable group by 1,3,4;  -- for simple scenario of continuos dates are assured.

For other cases need to go with a selfjoin...will try and let you know.

Enthusiast

Re: Simple merging of records based on Date

Junior Contributor

Re: Simple merging of records based on Date

This will normalize both overlapping and continous periods. It looks for gaps in the Derived Table (current start date greater than maximum previous end date) and then constructs the end date in the outer Select:

SELECT Eno, Name, strt_date, 
COALESCE(MAX(x)
OVER (PARTITION BY Name
ORDER BY strt_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), x2)
FROM
(
SELECT t.*,
MAX(end_date) -- maximum previous end date
OVER (PARTITION BY Name
ORDER BY strt_date,end_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS x,
MAX(end_date)
OVER (PARTITION BY Name) AS x2 -- the maximum end date
FROM tab AS t
QUALIFY strt_date -1 > x -- first row after a gap
OR x IS NULL -- first row in a partition
) AS dt