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!

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

Newbie_820-Oracle

I'm looking here also - https://docs.oracle.com/database/apex-5.1/HTMDB/sending-email-from-an-application.htm#HTMDB29887

Newbie_820-Oracle

And here: https://docs.oracle.com/database/121/AEAPI/apex_mail.htm#AEAPI341
Just not sure how to trigger it from a calendar's due date.

Jeroen.

I'm using Automations to 'trigger' emails. Works great!
Automate your business process in Oracle APEX 20.2 | Oracle Application Express Blog
Regards,
Jeroen

1 - 3
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
535 views