Table1: TRANSACTION
FIELDS : AGENT_ID, TRNX_AMT, INSERTED_ON
create table TRANSACTION (AGENT_ID varchar2(10), TRN_AMT number(4,2),INSERTED_ON date);
Table2: AGENT_DETAILS
FIELDS: AGNET_ID,NAME,STATUS
create table AGENT_DETAILS(AGENT_ID varchar2(10), NAME varchar2(20),STATUS varchar2(10));
Data on TRANSACTION table
insert into TRANSACTION values( '1111',50.00,to_date('02-FEB-17'));
insert into TRANSACTION values( '1111',50.00,to_date('03-FEB-17'));
insert into TRANSACTION values( '2222',10.00,to_date('03-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('06-FEB-17'));
insert into TRANSACTION values( '1111',15.00,to_date('06-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('07-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('08-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('09-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('10-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('11-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('12-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('13-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('13-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('14-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('15-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('16-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('17-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('18-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('19-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('20-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('23-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('24-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('26-FEB-17'));
insert into TRANSACTION values( '1111',40.00,to_date('27-FEB-17'));
insert into TRANSACTION values( '2222',40.00,to_date('27-FEB-17'));
Data on Agent Table
insert into AGENT_DETAILS values ('1111','Test1','ACTIVE');
insert into AGENT_DETAILS values ('2222','Test2','ACTIVE');
Some history before i get to the problem, agent would be posting transaction each of which is updated on TRANSACTION table.
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
AGENT_ID, NO_OF_DAYS
1111,1
2222,2
For AGNET_ID - 1111 its 1 day - 12th Feb
For AGNET_ID - 2222 its 2 days - 19th and 26 Feb
You can find the date of the previous transaction for an agent using lag(). So to find the number of days between two transactions subtract lag() from inserted_on:
inserted_on - lag(inserted_on) over (partition by agent_id order by inserted_on)
Then filter the result of this where this differences is >= 5. And group by agent_id and count:
with diffs as (
select t.*, inserted_on - lag(inserted_on) over (partition by agent_id order by inserted_on) d
from transaction t
order by agent_id, inserted_on
)
select agent_id, count(*)
from diffs
where d >= 5
group by agent_id;
AGENT_ID COUNT(*)
1111 1
2222 2
PS: remember to use 4 digits years in dates!