Can you please help on query to set flag ,
T_ID T_DATE B_ID I_ID BUY_SELL FLAG
A 10-Feb-16 123 ABC B NOT OK
A 11-Feb-16 123 ABC S NOT OK
A 10-Dec-16 123 ABC B NOT OK
A 4-Jan-16 123 ABC B NOT OK
P 1-Feb-16 456 PQR B OK
P 1-Dec-15 456 PQR B OK
P 2-Dec-15 456 PQR B OK
L 2-Mar-16 789 XYZ B OK
L 1-Nov-15 789 XYZ S OK
B 2-Mar-16 789 LMN B NOT OK
B 2-Feb-16 789 LMN S NOT OK
logic to set Flag is : for every B_ID, I_ID combination check max(T_DATE) and traceback to 60 days .
If we have 'B' and 'S' for BUY_SELL with in 60days then set flag as 'NOT OK'
else 'OK'
I tried using analytical function but not getting logic right
In above case
T_ID A is not ok
P is ok because it has only 'B' even though the T_DATE has 60 days of transaction data
L is ok because it dont have 60 days
B is not ok (same as A)
CREATE TABLE "TRAD_AGE" ("T_ID" VARCHAR2(1), "T_DATE" DATE, "B_ID" VARCHAR2(3), "I_ID" VARCHAR2(3), "BUY_SELL" VARCHAR2(1))
REM INSERTING into TRAD_AGE
SET DEFINE OFF;
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('A',to_date('10-FEB-16','DD-MON-RR'),'123','ABC','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('A',to_date('11-FEB-16','DD-MON-RR'),'123','ABC','S');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('A',to_date('10-DEC-16','DD-MON-RR'),'123','ABC','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('A',to_date('04-JAN-16','DD-MON-RR'),'123','ABC','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('P',to_date('01-FEB-16','DD-MON-RR'),'456','PQR','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('P',to_date('01-DEC-15','DD-MON-RR'),'456','PQR','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('P',to_date('02-DEC-15','DD-MON-RR'),'456','PQR','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('L',to_date('02-MAR-16','DD-MON-RR'),'789','XYZ','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('L',to_date('01-NOV-15','DD-MON-RR'),'789','XYZ','S');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('B',to_date('02-MAR-16','DD-MON-RR'),'789','LMN','B');
Insert into TRAD_AGE (T_ID,T_DATE,B_ID,I_ID,BUY_SELL) values ('B',to_date('02-FEB-15','DD-MON-RR'),'789','LMN','S');
Thanks and Regards
Sumanth