Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Overlapping Dates that need to be sequenced...

spalato76Apr 22 2010 — edited Nov 3 2010
Hi All,

I have the following table of data below:

-----------------------------------------------------------------------
ID START_DT END_DT
-----------------------------------------------------------------------
1 01-Apr-08 07-Apr-08
1 08-Apr-08 11-Apr-08
1 13-Apr-08 14-Apr-08
1 17-Apr-08 21-Apr-08
2 18-Apr-08 22-Apr-08
2 22-Apr-08 26-Apr-08
3 20-Apr-08 24-Apr-08

I want to add an additional column to the above table and sequence the records as such:

-----------------------------------------------------------------------
ID START_DT END_DT EPISODE
-----------------------------------------------------------------------
1 01-Apr-08 07-Apr-08 1
1 08-Apr-08 11-Apr-08 1
1 13-Apr-08 14-Apr-08 2
1 17-Apr-08 21-Apr-08 3
2 18-Apr-08 22-Apr-08 1
2 22-Apr-08 26-Apr-08 1
3 20-Apr-08 24-Apr-08 1

Essentially, I want to take my data, group by the ID and order by End Date....if the next Start Dt for the same ID is on the same day or plus 1 day of the previous End Dt it considers this record to be overlapping or a part of the first record and gives it the same episode number. If it's greater than 1 day then it doesn't consider it to be an overlapping record or part of the previous record and increments the Episode Number by 1.

Ultimately, once I have the above table, I'll collapse it and get the following final result:
-----------------------------------------------------------------------
ID MIN_START_DT MAX_END_DT EPISODE
-----------------------------------------------------------------------
1 01-Apr-08 11-Apr-08 1
1 13-Apr-08 14-Apr-08 2
1 17-Apr-08 17-Apr-08 3
2 18-Apr-08 26-Apr-08 1
3 20-Apr-08 24-Apr-08 1

I've been able to do this successfully using a cursor for loop but I have hundreds of thousands to potentially millions of records so the cursor loop runs very slowly and I was hoping there would be a more efficient way to do this, perhaps using a combination of lead and lag and partition over.

Help would be appreciated.

EK

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 26 2010
Added on Apr 22 2010
11 comments
4,889 views