Combining records based on max and min values

General
Enthusiast

Combining records based on max and min values

Hi there,

I have a list of records that have a status start and end date, and in many cases they are contiguous. I'm trying to figure out a way to combine these into one record, provided all other fields contain the distinct data.

 

Sample data:

 

Firstname, Lastname, Status, Start_Date, End_Date

Jon, Smith, A, 1/4/2015, 1/5/2015

Jon, Smith, A, 1/5/2015, 1/10/2015

Jon, Smith, A, 1/10/2015, 1/25/2015

 

Trying to figure out a query that will yield the below result:

 

Firstname, Lastname, Status, Start_Date, End_Date

Jon, Smith, A, 1/4/2015, 1/25/2015

 

2 REPLIES
Highlighted
Teradata Employee

Re: Combining records based on max and min values

Consider the TD_NORMALIZE_MEET table function - see the SQL Reference: SQL Functions, Operators, Expressions, and Predicates manual for details.

Junior Contributor

Re: Combining records based on max and min values

Or apply NORMALIZE :-)

 

SELECT Firstname, Lastname, Status, Begin(pd), End(pd)
FROM
 (
   SELECT NORMALIZE Firstname, Lastname, Status, PERIOD(Start_Date, End_Date) AS pd
   FROM tab
 ) AS dt