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

Compare rows (records) in a table

Compare rows (records) in a table

(OP)
Hello,

I need help in comparing rows in a table. The structure of the table and data are as follows (the data is ordered by PtID, start_date and stop_date)

PtID START_DATE STOP_DATE
1 04/23/14 04/24/14
1 04/24/14 04/27/14
1 04/27/14 04/28/14
2 01/11/14 01/16/14
2 02/03/14 02/03/14
2 02/04/14 02/04/14
2 02/05/14 02/07/14
2 02/07/14 02/08/14

For each PtID i need to compare the stop_date of the first with the start_date of the next record. If the difference is <=1 I have to update the stop_date of the first record with the stop_date of the second record and then delete the second record. If the difference is > 1 then I have to leave the record as is and compare the second record with the third record. So the resulting table should be as follows:

PtID START_DATE STOP_DATE
1 04/23/14 04/28/14
2 01/11/14 01/16/14
2 02/03/14 02/08/14

I am new to Oracle so I am not familiar as to how to go about this task. Any help is appreciated.

Thanks!

RE: Compare rows (records) in a table

This should get you started down the right track:

CODE

select ptid, stopdate, lead (startdate, 1) over (partition by PTID order by startdate, stopdate)
from table 

Once you have all the values lined up right, then you can implement the update.

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