Team,

Please find below the Create Table stmt

CREATE TABLE ST_TA_STOP_INTEREST(LOAN_CODE NUMBER, TRANSACTION_DATE DATE,EVENT_ID VARCHAR2(5));

Please find below the insert stmts for the above created table:

SET DEFINE OFF;
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('07/29/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('08/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RST');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('08/16/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('08/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RST');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('08/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('08/31/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RST');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('09/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('09/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RST');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('09/15/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('09/30/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RST');
Insert into ST_TA_STOP_INTEREST
   (LOAN_CODE, TRANSACTION_DATE, EVENT_ID)
 Values
   (1001, TO_DATE('10/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'STP');
COMMIT;

In the given ST_TA_STOP_INTEREST table loan_Code indicates the loan code of the customer. Here we have only one loan code 1001 . Transaction_date indicates the date the transaction has been posted.Event_id indicates the id of the event.

In the event_id column STP STANDS for "STOP Interest" and RST indicates "START INTEREST".


In other words if the record has STP as event_id then , in that scenario Interest calculation has to be stopped till RST record(even_id has to be RST).

So here if we see the data in the table , order by the transaction_date column, on 07/29/2017 we see record with STP as event_id and the next record in the table is with event_id RST and transaction_date as 08/05/2017.

In other words Interest calculation has to be stopped from 07/29/2017 to 08/04/2017 and interest calculation has to started from 08/05/2017 to 08/16/2017(as per the next record in the ST_TA_STOP_INTEREST table).


The inserts in the table for any loan_code will start with a STP as event_id , but the last record for that loan_code can have event_id as STP or RST.

Moreover each STP has to be followed by RST and viceversa.

If we have last record for that loan_code ,withe event_id as STP then interest calculation has to be stopped from that date till future date of RST as event_id(Currently future date may not be available as in this table, but the last record in on 10/10/2017 with with event_id as STP).

If we have last records for that loan_Code ,with event_id as RST then interest calculation has to be started from that date till future date of STP as event_id(Currently future date may not be there ).

Now I would like to calculate the count of overall days for which interest has to be calculated in the given date range .

The date range can be anything from 01/01/2017 to 12/31/2017.

In other words, the difference would be calucated as: ((Difference between the given date range)- (the duration in which the interest calcultion has to be stopped(count of all days between STP and RST in the given order of the Transaction date)))

For ex:

If we give the date range as 07/29/2017 to 08/04/2017 then the diffenece days has to be 0, because the interest calculation has been stopped during those days duration.

If we give the date range as 07/25/2017 and 07/28/2017 then the difference days has to be 3, since there is no stoppage of interest calculation during those days.

If we give the date range as 10/05/2017 and 10/19/2017 then the difference days has to be 4,since from 10/05/2017 to 10/09/2017 the interest is in start state and the
difference days is 4 and from 10/10/2017 it has to be stopped.

If there are no records for given date ranges then , we need to get the difference between those days.

Preferably we are looking for a function , that should handle this situation

Thanking you in anticipation.

var st varchar2(10);
var en varchar2(10);

exec :st := '2017-07-25';
exec :en := '2017-10-25';
with ranges as (
  select s.*, 
         lead( TRANSACTION_DATE ) 
           over (order by TRANSACTION_DATE) next_trans_date
  from   ST_TA_STOP_INTEREST s
), dates as (
  select to_date(:st, 'yyyy-mm-dd')+rownum-1 dt from dual
  connect by level <= to_date(:en, 'yyyy-mm-dd') - to_date(:st, 'yyyy-mm-dd') + 1
), interest_dates as (
  select d.dt, r.*, 
         min(d.dt) over () mn_dt, 
         max(d.dt) over () mx_dt
  from   dates d
  left join ranges r
  on     d.dt >= r.TRANSACTION_DATE 
  and    ( d.dt < r.next_trans_date or r.next_trans_date is null)
)
  select count(*)
  from   interest_dates
  where  event_id = 'RST' or event_id is null;

COUNT(*)  
39