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.

Active Period query

548617Nov 22 2010 — edited Nov 26 2010
Hi All,

I need a little help in figuring out this logic. There is table called status with 3 columns id,status,status_date and here is the sample data

100 A 01-Jan-2010
100 A 02-Jan-2010
100 I 03-Jan-2010
100 I 01-Feb-2010
100 A 01-Apr-2010
100 A 02-Apr-2010
100 A 03-Apr-2010
100 I 01-May-2010
100 A 01-jun-2100
100 I 01-Sep-2010

An active period is defined as the period between first status of A and first status of I.

so the output for active periods of A should be
01-Jan-2010 to 03-Jan-2010
01-Apr-2010 to 01-May-2010
01-Jun-2010 to 01-Sep-2010

The logic I wrote using lead analytical function is failing because i am looking at current status of 'A' and next status of 'I' and the output I get is
02-Jan-2010 to 03-Jan-2010
03-Apr-2010 to 01-May-2010
01-Jun-2010 to 01-Sep-2010

Any help is figuring out the logic is greatly appreciated.

Thanks and regards
Hari

Comments

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

Post Details

Locked on Dec 20 2010
Added on Nov 22 2010
9 comments
486 views