Code:
Scripts:
CREATE TABLE "REFERENCE_TABLE"
(
"PRODUCT" NUMBER(4, 0),
"EFF_FROM_DATE" DATE,
"EFF_TO_DATE" DATE,
"TYPE" CHAR(2 BYTE),
"MIN_AMT" NUMBER(10, 0),
"MAX_AMT" NUMBER(10, 0),
"CHARGE" NUMBER(5, 2)
);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T1',0,15,0);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T2',16,1000,0.75);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T3',1001,2000,1.5);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T4',2001,5000,3);
CREATE TABLE "BAL"
(
"ID" NUMBER,
"BALANCE" NUMBER,
"BAL_DATE" DATE
);
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-11.71,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-405.71,to_date('02-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-760.71,to_date('03-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
CREATE TABLE "PERIODS"
(
"START_DATE" DATE,
"END_DATE" DATE,
"ID" NUMBER
);
Insert into periods (START_DATE,END_DATE,ID) values (to_date('02-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('13-OCT-05 00.00.00','DD-MON-RR HH24.MI.SS'),1);