Compare rows (records) in a table

[vgun]

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!

 

[yelworcm]

This should get you started down the right track:

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.