OraFAQ Forum: SQL & PL/SQL » To replace value where the date falls

Home » SQL & PL/SQL » SQL & PL/SQL » To replace value where the date falls (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To replace value where the date falls [message #643728]

Fri, 16 October 2015 06:41

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Hi Guys,

I am having below tables

Balance
     
ID	BAL_DATE	BAL	LIMIT_AMOUT
1234	01-Jan-08	-195.34	-5000
1234	02-Jan-08	-6000	-5000
1234	03-Jan-08	-209.84	-25
1234	04-Jan-08	-54.96	-25
1234	14-Oct-09	-195.34	-25
1234	16-Oct-09	-209.84	-25
1234	15-Jun-14	-195.34	-5000
1234	16-Jun-14	-2000	-5000
1234	19-Jun-14	-5500	-25

bucket
    
ID	CODE	START_DATE	END_DATE	PRODUCT	BUK_ID
879	1490	16-Nov-07	09-Oct-09	2300	1
879	3333	20-Nov-07	08-Oct-08	2300	4
879	3334	09-Oct-08	08-Oct-09	2300	4
879	1490	10-Oct-09	19-Nov-10	2	2
879	1490	10-Oct-09	19-Nov-10	2	2
879	1490	16-Jun-14	20-Aug-14	2300	3

ref_table_1
 
     
PRODUCT	EFF_FROM_DATE	EFF_TO_DATE	TYPE	MIN_AMT	MAX_AMT	RATE	CHARGE
2300	10-Jun-02	01-Jun-08	T1	0	0	0	
2300	10-Jun-02	01-Jun-08	T2	1	5000	14.628	
2300	10-Jun-02	01-Jun-08	T4			24.582	
2300	08-Jun-09	12-Jan-11	T1	0	0	0	
2300	08-Jun-09	12-Jan-11	T2	1	5000	17.82	
2300	08-Jun-09	12-Jan-11	T4			0	
2300	16-Jun-14	31-Dec-99	T1	0	15		0
2300	16-Jun-14	31-Dec-99	T2	16	1000		0.75
2300	16-Jun-14	31-Dec-99	T3	1001	2000		1.5
2300	16-Jun-14	31-Dec-99	T4	2001	5000		3

Output required
ID	BAL_DATE	T1VAL	T2VAL	T3VAL	T4VAL	T1PER	T2PER	T4PER
1234	01-Jan-08	200	5000	0	0	0	14.628	24.582
1234	02-Jan-08	200	5000	0	0	0	14.628	24.582
1234	03-Jan-08	200	5000	0	0	0	14.628	24.582
1234	04-Jan-08	200	5000	0	0	0	14.628	24.582
1234	16-Jun-14	15	1000	2000	5000			
1234	19-Jun-14	15	1000	2000	5000			

Scripts:

CREATE TABLE "BALANCE"
   ( "ID" NUMBER(10,0) NOT NULL ENABLE,
  "BAL_DATE" DATE NOT NULL ENABLE,
  "BAL" NUMBER(15,2) NOT NULL ENABLE,
  "LIMIT_AMOUT" NUMBER(15,2)
   );
 
  CREATE TABLE "BUCKET"
   ( "ID" VARCHAR2(10 BYTE),
  "CODE" NUMBER(4,0),
  "START_DATE" DATE,
  "END_DATE" DATE,
  "PRODUCT" VARCHAR2(20 BYTE),
  "BUK_ID" NUMBER
   );
 
CREATE TABLE ."REF_TAB_1"
   ( "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),
  "RATE" NUMBER(6,3),
  "CHARGE" NUMBER(5,2)
   );
 
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('01-JAN-08','DD-MON-RR'),-195.34,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('02-JAN-08','DD-MON-RR'),-6000,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('03-JAN-08','DD-MON-RR'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('04-JAN-08','DD-MON-RR'),-54.96,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('14-OCT-09','DD-MON-RR'),-195.34,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('16-OCT-09','DD-MON-RR'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('15-JUN-14','DD-MON-RR'),-195.34,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('16-JUN-14','DD-MON-RR'),-2000,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('19-JUN-14','DD-MON-RR'),-5500,-25);
 
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T1',0,0,0,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T2',1,5000,14.628,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T1',0,0,0,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T2',1,5000,17.82,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T4',null,null,0,null);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T1',0,15,null,0);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T2',16,1000,null,0.75);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T3',1001,2000,null,1.5);
Insert into ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T4',2001,5000,null,3);
 
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('16-NOV-07','DD-MON-RR'),to_date('09-OCT-09','DD-MON-RR'),'2300',1);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3333,to_date('20-NOV-07','DD-MON-RR'),to_date('08-OCT-08','DD-MON-RR'),'2300',4);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3334,to_date('09-OCT-08','DD-MON-RR'),to_date('08-OCT-09','DD-MON-RR'),'2300',4);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('10-OCT-09','DD-MON-RR'),to_date('19-NOV-10','DD-MON-RR'),'2',2);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('10-OCT-09','DD-MON-RR'),to_date('19-NOV-10','DD-MON-RR'),'2',2);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('16-JUN-14','DD-MON-RR'),to_date('20-AUG-14','DD-MON-RR'),'2300',3);

01. First to check if bal_date is falling between start and end dates of bucket and take the product and compare with eff_from_date and eff_to_date of ref_table_1

02. Take the respective max_amt and rate to corresponding dates

03. While checking the dates from bucket table dont consider code which starts with 3 or not like '3%'

04 If the product code in the BUCKET table does not start with 3, T1VAL should be pulled from from REF_TABLE.
If the product code in the BUCKET table starts with 3 and bal_date falling between Start and end dates of respective code which starts with 3 then , T1VAL should be 200.

Query i am using where i am struck

SELECT id,
   TB.BAL_DATE,
   MAX(DECODE(TRIM(b.TYPE),'T1',b.MAX_AMT)) T1VAL,
   MAX(DECODE(TRIM(b.TYPE),'T2',b.MAX_AMT)) T2VAL,
   MAX(DECODE(TRIM(b.TYPE),'T3',b.MAX_AMT))T3VAL,
   MAX(DECODE(TRIM(b.TYPE),'T4',b.MAX_AMT))T4VAL,
   MAX(DECODE(TRIM(b.TYPE),'T1',b.RATE)) T1PER,
   MAX(DECODE(TRIM(b.TYPE),'T2',b.RATE)) T2PER,
   MAX(DECODE(TRIM(b.TYPE),'T4',b.RATE)) T4PER
FROM balance TB,
   ref_tab_1 b,
  (SELECT DISTINCT start_date,
   end_date,
   product,
   buk_id
   FROM bucket BK
   WHERE id = 879
   AND code NOT LIKE '3%') bkt
WHERE bkt.product = b.product
  AND TB.BAL_DATE BETWEEN b.EFF_FROM_DATE AND b.EFF_TO_DATE
  AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
GROUP BY id,
   TB.BAL_DATE
ORDER BY tb.bal_date;

Report message to a moderator