Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

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
392 views