Main_table		
ID	DATE_COL	AMOUNT
3429	02-JAN-01 00.00.00	6
3429	01-DEC-01 00.00.00	-1
3429	01-DEC-01 00.00.00	6
3429	01-DEC-01 00.00.00	6
 
ref_table_1			
ID	START_DATE	END_DATE	PROD_ID
3429	22-AUG-00 00.00.00	26-OCT-15 00.00.00	2300
3429	22-AUG-00 00.00.00	26-OCT-15 00.00.00	
2300
  
Input	
ID	RATE
3429	
0.02191781
 
ref_table
PROD_ID	EFF_FROM_DATE	EFF_TO_DATE	PRODUCT_FEE
2300	27-JUN-96 00.00.00	09-APR-00 00.00.00	15.5
2300	27-JUN-96 00.00.00	09-APR-00 00.00.00	15.5
2300	27-JUN-96 00.00.00	09-APR-00 00.00.00	15.5
2300	10-APR-00 00.00.00	09-SEP-01 00.00.00	15.5
2300	10-APR-00 00.00.00	09-SEP-01 00.00.00	15.5
2300	10-APR-00 00.00.00	09-SEP-01 00.00.00	15.5
 
I need the output like this to include duplicate records from main_table
 
   
ID	AMOUNT	DATE_COL	FEE	NET_AMT	DAILY_INT_RATE
3429	6	02-JAN-01 00.00.00	15.5	-9.5	0.000219
3429	-1	01-DEC-01 00.00.00	0	-1	0.000219
3429	6	01-DEC-01 00.00.00	0	6	0.000219
3429	6	01-DEC-01 00.00.00	0	6	0.000219
 
logic to derive the values
 
01. check my date_col which falls in start and end dates of ref_table_1 and take the prod_id
02. from the prod_id compare it with ref_table of eff_from_date and eff_to_date and take the product fee
03.ref_table_1 and ref_table will have duplicated but need to take distinct dates from two
04. net_amt = amount - product_Fee

SELECT distinct
      FS.id,
      FS.amount ,
      FS.date_col ,
      NVL (PRODUCT_FEE,0) fee,
     CASE
          WHEN  FS.amount <0   THEN
           (NVL(FS.amount,0) - NVL(PRODUCT_FEE,0)/2)
          WHEN  FS.amount >=0 THEN
     (NVL (FS.amount ,0) - NVL (PRODUCT_FEE,0))
      END NET_AMT,
      (PARM.rate     / 100) DAILY_INT_RATE
    FROM main_table FS
          join  input PARM
          on FS.id = PARM.id
          and fs.id= 3429
          join  REF_TABLE_1 bkt
          on bkt.id = FS.id
          AND  FS.date_col BETWEEN bkt.start_date AND bkt.end_date
          AND bkt.id = fs.id
          AND bkt.id = 3429
          left outer join  ref_table TK on
          FS.date_col BETWEEN TK.EFF_FROM_DATE AND TK.EFF_TO_DATE
          and bkt.prod_id = TK.prod_id
order by fs.date_col;

ID	AMOUNT	DATE_COL	FEE	NET_AMT	DAILY_INT_RATE
3429	6	02-JAN-01 00.00.00	15.5	-9.5	0.0002191781
3429	-1	01-DEC-01 00.00.00	0	-1	0.0002191781
3429	6	01-DEC-01 00.00.00	0	6	0.0002191781
 

CREATE TABLE "MAIN_TABLE"
(
  "ID"       VARCHAR2(10 BYTE) NOT NULL ENABLE,
  "DATE_COL" DATE,
  "AMOUNT"   NUMBER(15, 2)
);
 
 
CREATE TABLE "REF_TABLE"
(
  "PROD_ID"       NUMBER(4, 0),
  "EFF_FROM_DATE" DATE,
  "EFF_TO_DATE"   DATE,
  "PRODUCT_FEE"   NUMBER(12, 2)
);
 
 
CREATE TABLE "REF_TABLE_1"
(
  "ID"         VARCHAR2(10 BYTE),
  "START_DATE" DATE,
  "END_DATE"   DATE,
  "PROD_ID"    VARCHAR2(20 BYTE)
);
 
 
CREATE TABLE "INPUT"
(
  "ID"   VARCHAR2(10 BYTE) NOT NULL ENABLE,
  "RATE" NUMBER(11, 8)
);
 
 
Insert into main_table (ID,DATE_COL,AMOUNT) values ('3429',to_date('02-JAN-01 00.00.00','DD-MON-RR HH24.MI.SS'),6);
Insert into main_table (ID,DATE_COL,AMOUNT) values ('3429',to_date('01-DEC-01 00.00.00','DD-MON-RR HH24.MI.SS'),6);
Insert into main_table (ID,DATE_COL,AMOUNT) values ('3429',to_date('01-DEC-01 00.00.00','DD-MON-RR HH24.MI.SS'),-1);
Insert into main_table (ID,DATE_COL,AMOUNT) values ('3429',to_date('01-DEC-01 00.00.00','DD-MON-RR HH24.MI.SS'),6);
 
 
Insert into ref_table_1 (ID,START_DATE,END_DATE,PROD_ID) values ('3429',to_date('22-AUG-00 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('26-OCT-15 00.00.00','DD-MON-RR HH24.MI.SS'),'2300');
Insert into ref_table_1 (ID,START_DATE,END_DATE,PROD_ID) values ('3429',to_date('22-AUG-00 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('26-OCT-15 00.00.00','DD-MON-RR HH24.MI.SS'),'2300');
 
 
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('27-JUN-96 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('27-JUN-96 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('27-JUN-96 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('10-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-SEP-01 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('10-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-SEP-01 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
Insert into ref_table (PROD_ID,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE) values (2300,to_date('10-APR-00 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-SEP-01 00.00.00','DD-MON-RR HH24.MI.SS'),15.5);
 
 
Insert into input (ID,RATE) values ('3429',0.02191781);