Thank you for the reply, but in case if the first transaction is done in the middle of the month the counts does not seem to match.

Fresh Data on TRANSACTION table

insert into TRANSACTION values( '1667',50.00,to_date('14-FEB-2017'));
insert into TRANSACTION values( '1667',50.00,to_date('17-FEB-2017'));

With the above data the lag query does not seem to fetch results.

I want the query to count the number of days after 5 continuous days, as days of absence.

For this Agnet_id from start of month to 13th its 8 days of absence and between 18th to 28th its 6 days

so the count of absence expected is 8+6=14 days...

insert into AGENT_DETAILS values ('1667','Test3','ACTIVE');

with dates as (
  select date'2017-02-01'+level-1 dt
  from   dual
  connect by level <= 28
) 
  select agent_id, count(*)
  from   dates
  cross  join agent_details ad
  where  not exists (
    select * from transaction t
    where  dt = inserted_on
    and   t.agent_id = ad.agent_id
  )
  group  by agent_id; 

AGENT_ID  COUNT(*)  
1111      14        
2222      17        
1667      26 

with dates as (
  select date'2017-02-01'+level-1 dt
  from   dual
  connect by level <= 28
), diffs as (
  select dt - row_number() over (partition by agent_id order by dt) grp, agent_id
  from   dates
  cross  join agent_details ad
  where  not exists (
    select * from transaction t
    where  dt = inserted_on
    and   t.agent_id = ad.agent_id
  )
)
  select agent_id, count(*) from diffs
  group  by agent_id, grp
  having count(*) >= 5;

AGENT_ID  COUNT(*)  
2222      6         
1111      6         
1667      13        
1667      11        
2222      6  

Further to my prev comment i have been able to make a bit of progress by changing the query as bellow

with diffs as (
select t.*, inserted_on - lag(inserted_on,1,20170201) over (partition by agent_id order by inserted_on) d
from transaction t
order by agent_id, inserted_on
)
select agent_id, sum(d-5)
from diffs
where d >= 5
group by agent_id;

but i am unable to fetch the count of days between 18th to 28th

Previously you said this to us.

.....
My problem is i need to identify the list of agent who have not performed any transaction for 5 continuous days in any given month.

Seeking help to build a query which would help identify the agent who have not performed transaction for 5 continuous days and give us this result
......


then you said this.


....
I want the query to count the number of days after 5 continuous days, as days of absence.
....


Those two looks very different to us.

for better clarity - here is the month of Feb 2017 Calender

demo@ORA12C> select *
  2  from (
  3  select x+level-1 dt ,
  4         to_char( x+level-1 ,'DY') dy,
  5         case when to_char( x+level-1 ,'DY') ='SUN'
  6            then to_number( to_char(x+level-1 ,'iw')) +1
  7            else to_number( to_char( x+level-1 ,'iw')) end iw
  8  from ( select to_date('01-feb-2017','dd-mon-yyyy') x from dual )
  9  connect by level <=28
 10       )
 11  pivot(
 12    max(dt)
 13    for dy in ('SUN','MON','TUE','WED','THU','FRI','SAT') )
 14  order by iw   ;

        IW 'SUN'       'MON'       'TUE'       'WED'       'THU'       'FRI'       'SAT'
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
         5                                     01-FEB-2017 02-FEB-2017 03-FEB-2017 04-FEB-2017
         6 05-FEB-2017 06-FEB-2017 07-FEB-2017 08-FEB-2017 09-FEB-2017 10-FEB-2017 11-FEB-2017
         7 12-FEB-2017 13-FEB-2017 14-FEB-2017 15-FEB-2017 16-FEB-2017 17-FEB-2017 18-FEB-2017
         8 19-FEB-2017 20-FEB-2017 21-FEB-2017 22-FEB-2017 23-FEB-2017 24-FEB-2017 25-FEB-2017
         9 26-FEB-2017 27-FEB-2017 28-FEB-2017

demo@ORA12C>

....
For this Agnet_id from start of month to 13th its 8 days of absence
....


looking at the above calender from 01-feb to 13-feb it looks 9 working days (excluding week ends)


....
between 18th to 28th its 6 days
....


again 18-feb to 28-feb excluding week ends has 7 working days.

so start with this.

demo@ORA12C> select * from transaction;

AGENT_ID      TRN_AMT INSERTED_ON
---------- ---------- -----------
1667               50 14-FEB-2017
1667               50 17-FEB-2017

demo@ORA12C> select agent_id, inserted_on end_dt,
  2             lag(inserted_on) over( order by inserted_on) as start_dt,
  3             inserted_on - lag(inserted_on) over( order by inserted_on) as diff
  4  from transaction ;

AGENT_ID   END_DT      START_DT          DIFF
---------- ----------- ----------- ----------
1667       14-FEB-2017
1667       17-FEB-2017 14-FEB-2017          3

demo@ORA12C> select agent_id , ( select count(*)
  2             from dual
  3             where to_char( trunc(start_dt,'mm') + level -1 ,'DY') not in ('SAT','SUN')
  4             connect by level <= start_dt - trunc(start_dt,'mm')  ) begining_cnt,
  5      ( select count(*)
  6        from dual
  7        where to_char( end_dt+level-1 ,'DY') not in ('SAT','SUN')
  8        connect by level <= last_day(end_dt) - end_dt ) ending_cnt
  9  from (
 10  select agent_id, inserted_on end_dt,
 11             lag(inserted_on) over( order by inserted_on) as start_dt,
 12             inserted_on - lag(inserted_on) over( order by inserted_on) as diff
 13  from transaction
 14      )
 15  where diff <= 5     ;

AGENT_ID   BEGINING_CNT ENDING_CNT
---------- ------------ ----------
1667                  9          7

demo@ORA12C>

is this what you are looking for? the above code assumes just one "agent_id" in case of multiple "agent_id" have the necessary "partition by" clause in place.

but where did you get the "only weekdays" requirement from?

that was a safe assumption made from this given statement.

For this Agnet_id from start of month to 13th its 8 days of absence and between 18th to 28th its 6 days

between 18th to 28th we have around 11 days, excluding the weekends, left with only 5 days that was close to the given 6 days.

I was so much bogged down with this feature and when i finally landed at a place where i could get a solution i seem to have not explained my problem completely and precisely.

Let me try one more time. I have a transaction table into which every transaction done by the agent is logged. We have a penalty clause for an agent which goes like "In a given month, after every 5th day of continuous non transaction the agent would be penalized X amount per day" so it means if agent does not perform any transaction for 6 continuous days he should be penalized for 1 day. There is no distinction between weekdays and weekends all days are included for calculation. This counter for absence get reset at the start of every month.

So in case of agent 1667 a penalty has to be calculated as, from start of month to 13th its 8 days of absence and between 18th to 28th its 6 days

so the count of absence expected is 8+6=14 days...

I apologize if i might have wasted your time without providing clarity on the question, and i hope this follow up is clear...

with dates as (
  select date'2017-02-01'+level-1 dt
  from   dual
  connect by level <= 28
), diffs as (
  select dt - row_number() over (partition by agent_id order by dt) grp, agent_id
  from   dates
  cross  join agent_details ad
  where  not exists (
    select * from transaction t
    where  dt = inserted_on
    and   t.agent_id = ad.agent_id
  )
)
  select agent_id, count(*) from diffs
  group  by agent_id, grp
  having count(*) >= 5;