Teradata SQL help needed 1

[lbzh]

I have some data in a table and looks like the Input table below.
I would like to write a Teradata SQL to inspect the sequential records represented in the Input.

If the key which is composed of Ordernum, Name and Zip is duplicated, then keep one version of the key record with the earliest Start Date from the first record and the latest Start Date from the last record in the group. To eliminate the dupe, the dupe records must be in succession.

If a record is not duped simply write it out to the output.


Input:
Ordernum Name Zip Start_Date End_Date
1 John 90210 3/15/2011 3/16/2011
1 John 90210 3/16/2011 3/18/2011
1 John 42538 3/18/2011 4/3/2011
1 John 90210 4/3/2011 4/9/2011
2 Jerry 23245 4/12/2011 4/15/2011
2 Paul 23245 4/15/2011 4/22/2011
2 Paul 23245 4/22/2011 4/29/2011

Output:
Ordernum Name Zip Start_Date End_Date
1 John 90210 3/15/2011 3/18/2011
1 John 42538 3/18/2011 4/3/2011
1 John 90210 4/3/2011 4/9/2011
2 Jerry 23245 4/12/2011 4/15/2011
2 Paul 23245 4/15/2011 4/29/2011

I know you would probably need an analytical function, but I am not sure how to begin with this.

 

[satishteradata]

HI

I think we can get the required output with the below query using qualify

select * from table_name
qualify row_number() over(partition by ordernum,name,zip order by start_date asc,end_date desc)=1