INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Teradata SQL help needed

Teradata SQL help needed

(OP)
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.

RE: Teradata SQL help needed

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close