Home » SQL & PL/SQL » SQL & PL/SQL » To Split data based on dates
| To Split data based on dates [message #643614] |
Tue, 13 October 2015 14:17  |
 |
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi,
I am having below tables
Balance:
ID BAL_DATE BAL LIMIT_AMOUT
1234 01-Jan-08 -195.34 -5000
1234 02-Jan-08 -209.84 -5000
1234 03-Jan-08 -209.84 -25
1234 04-Jan-08 -54.96 -25
1234 14-Oct-09 -195.34 -25
1234 15-Oct-09 -209.84 -25
1234 16-Oct-09 -209.84 -25
1234 17-Oct-09 -54.96 -25
1234 15-Jun-14 -195.34 -5000
1234 16-Jun-14 -209.84 -5000
1234 17-Jun-14 -209.84 -25
1234 18-Jun-14 -54.96 -25
1234 19-Jun-14 -2000.34 -25
1234 20-Jun-14 -209.84 -25
1234 21-Jun-14 -209.84 -25
1234 22-Jun-14 -54.96
-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-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
Reference_table
PRODUCT EFF_FROM_DATE EFF_TO_DATE TYPE MIN_AMT MAX_AMT RATE CHARGE
2 20-Oct-00 29-Mar-01 T1 0 250 0
2 20-Oct-00 29-Mar-01 T2 251 5000 17.4
2 20-Oct-00 29-Mar-01 T4 26.4
2 30-Mar-01 08-Jul-01 T1 0 250 0
2 30-Mar-01 08-Jul-01 T2 251 5000 17.394
2 30-Mar-01 08-Jul-01 T4 26.4
2 09-Jul-01 09-Sep-01 T1 0 250 0
2 09-Jul-01 09-Sep-01 T2 251 5000 11.431
2 09-Jul-01 09-Sep-01 T4 26.4
2 10-Sep-01 04-Jun-02 T1 0 250 0
2 10-Sep-01 04-Jun-02 T2 251 5000 11.431
2 10-Sep-01 04-Jun-02 T4 24.582
2 05-Jun-02 09-Jun-02 T1 0 250 0
2 05-Jun-02 09-Jun-02 T2 251 5000 9.523
2 05-Jun-02 09-Jun-02 T4 24.582
2 10-Jun-02 17-Aug-08 T1 0 250 0
2 10-Jun-02 17-Aug-08 T2 251 5000 11.431
2 10-Jun-02 17-Aug-08 T4 24.582
2 18-Aug-08 07-Jun-09 T1 0 250 0
2 18-Aug-08 07-Jun-09 T2 251 5000 11.431
2 18-Aug-08 07-Jun-09 T4 0
2 08-Jun-09 29-Oct-09 T1 0 250 0
2 08-Jun-09 29-Oct-09 T2 251 5000 14.013
2 08-Jun-09 29-Oct-09 T4 0
2 30-Oct-09 29-Apr-10 T1 0 250 0
2 30-Oct-09 29-Apr-10 T2 251 5000 15.76
2 30-Oct-09 29-Apr-10 T4 0
2 30-Apr-10 31-Aug-10 T1 0 250 0
2 30-Apr-10 31-Aug-10 T2 251 5000 17.82
2 30-Apr-10 31-Aug-10 T4 0
2 01-Sep-10 12-Jan-11 T1 0 999 0
2 01-Sep-10 12-Jan-11 T2 1000 15000 14.013
2 01-Sep-10 12-Jan-11 T4 0
2 13-Jan-11 15-Jun-14 T1 0 999 0
2 13-Jan-11 15-Jun-14 T2 1000 15000 13.97
2 13-Jan-11 15-Jun-14 T4 0
2300 27-Jun-96 09-Apr-00 T1 0 0 0
2300 27-Jun-96 09-Apr-00 T2 1 5000 17.4
2300 27-Jun-96 09-Apr-00 T4 26.41
2300 10-Apr-00 09-Sep-01 T1 0 0 0
2300 10-Apr-00 09-Sep-01 T2 1 5000 17.394
2300 10-Apr-00 09-Sep-01 T4 26.4
2300 10-Sep-01 04-Jun-02 T1 0 0 0
2300 10-Sep-01 04-Jun-02 T2 1 5000 14.628
2300 10-Sep-01 04-Jun-02 T4 24.582
2300 05-Jun-02 09-Jun-02 T1 0 0 0
2300 05-Jun-02 09-Jun-02 T2 1 5000 9.523
2300 05-Jun-02 09-Jun-02 T4 24.582
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 02-Jun-08 17-Aug-08 T1 0 0 0
2300 02-Jun-08 17-Aug-08 T2 1 5000 16.623
2300 02-Jun-08 17-Aug-08 T4 24.582
2300 18-Aug-08 07-Jun-09 T1 0 0 0
2300 18-Aug-08 07-Jun-09 T2 1 5000 16.623
2300 18-Aug-08 07-Jun-09 T4 0
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 13-Jan-11 15-Jun-14 T1 0 0 0
2300 13-Jan-11 15-Jun-14 T2 1 5000 17.777
2300 13-Jan-11 15-Jun-14 T4 0
2300 15-Jun-14 15-Aug-14 T1 0 500 0.5
2300 15-Jun-14 15-Aug-14 T2 501 1000 0.75
2300 15-Jun-14 15-Aug-14 T3 1001 2000 1.5
2300 15-Jun-14 15-Aug-14 T4 1001 1.5
Output:
ID_1 BAL_DATE T1 T2 T3 T4 T1PER T2PER T4PER T1_DERIVED T2_DERIVED T3_DERIVED T4_DERIVED REFAM BM BUK_ID T2MAX
1234 01-Jan-08 195 0 0 0 0 14.628 24.582 0 0.07815 0 0 -5000 2300 1 5000
1234 02-Jan-08 200 10 0 0 0 14.628 24.582 0 0.084161 0 0 -5000 2300 1 5000
1234 03-Jan-08 0 25 0 185 0 14.628 24.582 0 0.010019 0 0.124594 -25 2300 1 5000
1234 04-Jan-08 0 25 0 30 0 14.628 24.582 0 0.010019 0 0.020204 -25 2300 1 5000
1234 14-Oct-09 25 0 0 170 0 14.013 0 0 0 0 0 -25 2 2 5000
1234 15-Oct-09 25 0 0 185 0 14.013 0 0 0 0 0 -25 2 2 5000
1234 16-Oct-09 25 0 0 185 0 14.013 0 0 0 0 0 -25 2 2 5000
1234 17-Oct-09 25 0 0 30 0 14.013 0 0 0 0 0 -25 2 2 5000
1234 16-Jun-14 210 0 0 0 0 0 0 0.5 0 0 0 -5000 2300 3 1000
1234 17-Jun-14 210 0 0 0 0 0 0 0.5 0 0 0 -25 2300 3 1000
1234 18-Jun-14 55 0 0 0 0 0 0 0.5 0 0 0 -25 2300 3 1000
1234 19-Jun-14 500 1000 500 0 0 0 0 0.5 0.75 1.5 0 -25 2300 3 1000
1234 20-Jun-14 210 0 0 0 0 0 0 0.5 0 0 0 -25 2300 3 1000
1234 21-Jun-14 210 0 0 0 0 0 0 0.5 0 0 0 -25 2300 3 1000
1234 22-Jun-14 55 0 0 0 0 0 0 0.5 0 0 0 -25 2300 3 1000
Split the bal from balance table by referring to the ref_table in respective T1,T2,T3,T4 where balance falling in that range
Also consider LIMIT_AMOUT from balance table for splitting the records
If the bal is >=15-JUN-2014 then consider the charge column from ref_table and split the balances accordingly without calculating also not check the limit_amout
If bal_date is falling between start and end dates of bucket where code is 3333 then and should not be included due to overlap dates
T1 MAX_AMT - 200
T2 MIN_AMT -201 MAX_AMT - 5000
Query i have used:
SELECT ID_1,
BAL_DATE,
T1,
T2,
0 T3,
GREATEST(BAL -(T1 + T2),0) T4,
T1PER,
T2PER,
T4PER,
ROUND((T1 * T1PER) /(365 * 100),6) T1_derived,
ROUND((T2 * T2PER) /(365 * 100),6) T2_derived,
ROUND((GREATEST(BAL -(T1 + T2),0) * T4PER) /(365 * 100),6) T4_derived,
REFAM,
BM,
buk_id,
T2MAX
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
T1,
GREATEST(LEAST(T2VAL,BAL - T1,DECODE(SIGN(REFAM),1,0,ABS(REFAM)) - T1),0) T2,
T1PER,
T2PER,
T4PER,
BM,
buk_id,
T2MAX
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
LEAST(T1VAL,BAL,DECODE(SIGN(REFAM),1,0,ABS(REFAM))) T1,
NVL(T2VAL,0) T2VAL,
T1PER,
T2PER,
T4PER,
BM,
buk_id,
T2VAL T2MAX
FROM
(SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879 and code <>3333
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
)
)
)
order by BAL_DATE;
Scripts for table creation
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 "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 "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),
"RATE" NUMBER(6,3),
"CHARGE" NUMBER(5,2)
);
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-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);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('27-JUN-96','DD-MON-RR'),to_date('09-APR-00','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-APR-00','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('02-JUN-08','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T1',0,0,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('27-JUN-96','DD-MON-RR'),to_date('09-APR-00','DD-MON-RR'),'T2',1,5000,17.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-APR-00','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T2',1,5000,17.394,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T2',1,5000,14.628,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T2',1,5000,9.523,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('02-JUN-08','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T2',1,5000,16.623,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T2',1,5000,16.623,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T2',1,5000,17.777,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('27-JUN-96','DD-MON-RR'),to_date('09-APR-00','DD-MON-RR'),'T4',null,null,26.41,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-APR-00','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T4',null,null,26.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('02-JUN-08','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('20-OCT-00','DD-MON-RR'),to_date('29-MAR-01','DD-MON-RR'),'T2',251,5000,17.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('20-OCT-00','DD-MON-RR'),to_date('29-MAR-01','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('20-OCT-00','DD-MON-RR'),to_date('29-MAR-01','DD-MON-RR'),'T4',null,null,26.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-MAR-01','DD-MON-RR'),to_date('08-JUL-01','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-MAR-01','DD-MON-RR'),to_date('08-JUL-01','DD-MON-RR'),'T2',251,5000,17.394,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-MAR-01','DD-MON-RR'),to_date('08-JUL-01','DD-MON-RR'),'T4',null,null,26.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('09-JUL-01','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('09-JUL-01','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T2',251,5000,11.431,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('09-JUL-01','DD-MON-RR'),to_date('09-SEP-01','DD-MON-RR'),'T4',null,null,26.4,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T2',251,5000,11.431,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-SEP-01','DD-MON-RR'),to_date('04-JUN-02','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T2',251,5000,9.523,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('05-JUN-02','DD-MON-RR'),to_date('09-JUN-02','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T2',251,5000,11.431,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T2',251,5000,11.431,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('18-AUG-08','DD-MON-RR'),to_date('07-JUN-09','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T2',251,5000,14.013,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-OCT-09','DD-MON-RR'),to_date('29-APR-10','DD-MON-RR'),'T2',251,5000,15.76,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-OCT-09','DD-MON-RR'),to_date('29-APR-10','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-OCT-09','DD-MON-RR'),to_date('29-APR-10','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-APR-10','DD-MON-RR'),to_date('31-AUG-10','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-APR-10','DD-MON-RR'),to_date('31-AUG-10','DD-MON-RR'),'T2',251,5000,17.82,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('30-APR-10','DD-MON-RR'),to_date('31-AUG-10','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('01-SEP-10','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T1',0,999,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('01-SEP-10','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T2',1000,15000,14.013,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('01-SEP-10','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T1',0,999,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T2',1000,15000,13.97,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('13-JAN-11','DD-MON-RR'),to_date('15-JUN-14','DD-MON-RR'),'T4',null,null,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('15-JUN-14','DD-MON-RR'),to_date('15-AUG-14','DD-MON-RR'),'T1',0,500,null,0.5);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('15-JUN-14','DD-MON-RR'),to_date('15-AUG-14','DD-MON-RR'),'T2',501,1000,null,0.75);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('15-JUN-14','DD-MON-RR'),to_date('15-AUG-14','DD-MON-RR'),'T3',1001,2000,null,1.5);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2300,to_date('15-JUN-14','DD-MON-RR'),to_date('15-AUG-14','DD-MON-RR'),'T4',1001,null,null,1.5);
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'),-209.84,-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('15-OCT-09','DD-MON-RR'),-209.84,-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('17-OCT-09','DD-MON-RR'),-54.96,-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'),-209.84,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('17-JUN-14','DD-MON-RR'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('18-JUN-14','DD-MON-RR'),-54.96,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('19-JUN-14','DD-MON-RR'),-2000.34,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('20-JUN-14','DD-MON-RR'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('21-JUN-14','DD-MON-RR'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('22-JUN-14','DD-MON-RR'),-54.96,-25);
|
|
|
|
| Re: To Split data based on dates [message #643632 is a reply to message #643614] |
Wed, 14 October 2015 05:51  |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi All,
can you help, i have reduced test cases something like below
Balance
ID BAL_DATE BAL LIMIT_AMOUT
1234 01-Jan-08 -195.34 -5000
1234 02-Jan-08 -209.84 -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 -209.84 -5000
1234 19-Jun-14 -2000.34 -25
Reference Table
PRODUCT EFF_FROM_DATE EFF_TO_DATE TYPE MIN_AMT MAX_AMT RATE CHARGE
2 10-Jun-02 17-Aug-08 T1 0 250 0
2 10-Jun-02 17-Aug-08 T2 251 5000 11.431
2 10-Jun-02 17-Aug-08 T4 24.582
2 08-Jun-09 29-Oct-09 T1 0 250 0
2 08-Jun-09 29-Oct-09 T2 251 5000 14.013
2 08-Jun-09 29-Oct-09 T4 0
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
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-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
Output required
ID_1 BAL_DATE T1 T2 T3 T4 T1PER T2PER T3PER T4PER T1_DERIVED T2_DERIVED T3_DERIVED T4_DERIVED REFAM BM BUK_ID T2MAX
1234 01-Jan-08 195 0 0 0 0 14.628 0 24.582 0 0.07815 0 0 -5000 2300 1 5000
1234 02-Jan-08 200 10 0 0 0 14.628 0 24.582 0 0.084161 0 0 -5000 2300 1 5000
1234 03-Jan-08 0 25 0 185 0 14.628 0 24.582 0 0.010019 0 0.124594 -25 2300 1 5000
1234 04-Jan-08 0 25 0 30 0 14.628 0 24.582 0 0.010019 0 0.020204 -25 2300 1 5000
1234 14-Oct-09 25 0 0 170 0 14.013 0 0 0 0 0 0 -25 2 2 5000
1234 16-Oct-09 25 0 0 185 0 14.013 0 0 0 0 0 0 -25 2 2 5000
1234 16-Jun-14 15 195 0 0 0 0.75 1.5 3 0 0.75 0 0 -5000 2300 3 1000
1234 19-Jun-14 15 1000 985 0 0 0.75 1.5 3 0 0.75 1.5 3 -25 2300 3 1000
Query i have used
SELECT ID_1,
BAL_DATE,
T1,
T2,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t3val, GREATEST (BAL - t1val - t2val, 0)) else 0 end T3,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t3val, GREATEST (BAL - t1val - t2val-t3val, 0)) else GREATEST(BAL -(T1 + T2),0) end T4,
T1PER,
T2PER,
T3PER,
T4PER,
case when BAL_DATE >= date '2014-06-15' then T1PER else ROUND((T1 * T1PER) /(365 * 100),6) end T1_derived,
case when BAL_DATE >= date '2014-06-15' then T2PER else ROUND((T2 * T2PER) /(365 * 100),6) end T2_derived,
case when BAL_DATE >= date '2014-06-15' then T3PER else 0 end T3_derived,
case when BAL_DATE >= date '2014-06-15' then T4PER else ROUND((GREATEST(BAL -(T1 + T2),0) * T4PER) /(365 * 100),6) end T4_derived,
REFAM,
BM,
buk_id,
T2MAX
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
T1,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t2val, GREATEST (BAL - t1val, 0)) else
GREATEST(LEAST(T2VAL,BAL - T1,DECODE(SIGN(REFAM),1,0,ABS(REFAM)) - T1),0) end T2,
T1PER,
T2PER,
T3PER,
T4PER,
BM,
buk_id,
T2MAX,t2val,T1VAL,T3VAL
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t1val, BAL) else
LEAST(T1VAL,BAL,DECODE(SIGN(REFAM),1,0,ABS(REFAM))) end T1,
NVL(T2VAL,0) T2VAL,
case when BAL_DATE >= date '2014-06-15' then T1CHARGE
else T1PER end T1PER,
case when BAL_DATE >= date '2014-06-15' then T2CHARGE else T2PER end T2PER,
case when BAL_DATE >= date '2014-06-15' then T3CHARGE else 0 end T3PER,
case when BAL_DATE >= date '2014-06-15' then T4CHARGE else T4PER end T4PER,
BM,
buk_id,
T2VAL T2MAX,T1VAL,T3VAL
FROM
(SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879 and code <>3333
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
)
)
)
order by BAL_DATE;
1 Check the bal_date which falls between start and end dates of bucket , here it falls in 1 and 4 buk_id, but need to ignore if code is 3333, hence below record will be selected
879 1490 16-Nov-07 09-Oct-09 2300 1
2 Take the product and compare with reference table where it falls in below periods
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
3 Since the bal_date is falling in bucket table of code 3333 then consider below ranges
2300 10-Jun-02 01-Jun-08 T1 0 200 0
2300 10-Jun-02 01-Jun-08 T2 201 5000 14.628
2300 10-Jun-02 01-Jun-08 T4 24.582
4 Split the balance
ID_1 BAL_DATE T1 T2 T3 T4 T1PER T2PER T4PER T1_DERIVED T2_DERIVED T4_DERIVED REFAM BM BUK_ID T2MAX
1234 01-Jan-08 195 0 0 0 0 14.628 24.582 0 0.07815 0 -5000 2300 1 5000
T1_DERIVED T1*T1PER/365
T2_DERIVED T2*T2PER/365
T4_DERIVED T4*T4PER/365
|
|
|
|
| Re: To Split data based on dates [message #643643 is a reply to message #643632] |
Wed, 14 October 2015 14:27 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Guys can any one advise??
Scripts for test case
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'),-209.84,-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'),-209.84,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('19-JUN-14','DD-MON-RR'),-2000.34,-25);
Insert into reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (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 reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T2',251,5000,11.431,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('10-JUN-02','DD-MON-RR'),to_date('17-AUG-08','DD-MON-RR'),'T4',null,null,24.582,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T1',0,250,0,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T2',251,5000,14.013,null);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) values (2,to_date('08-JUN-09','DD-MON-RR'),to_date('29-OCT-09','DD-MON-RR'),'T4',null,null,0,null);
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-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);
commit;
[Updated on: Wed, 14 October 2015 14:57] Report message to a moderator |
|
|
|
|
|
|
|
| Re: To Split data based on dates [message #643793 is a reply to message #643701] |
Sun, 18 October 2015 15:34 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have reviewed this post and the one on the OTN forums ( https://community.oracle.com/thread/3801794 ). You have done a good job of providing statements to create the tables and insert sample data and shown the results that you want and what you have tried. I ran what you have tried and compared it to what you want and it looks like only the T1, T2, and T4 values are different from what you want. However, I am unable to understand how the desired results should be obtained. The inline views (sub-queries in the from clause) that you are using are a good method for processing such step by step things, where the innermost sub-query is the first step and the outermost query obtains the final results. What you might try doing is testing one sub-query at a time, starting with just the innermost, then the next one, and so on, and see at what point the results are not what you expect. Starting with the first row, I can't see where you get 195 for T1 instead of T2. I can't tell if you have made an error in your desired results or if your explanation or query is wrong.
|
|
|
|
| Re: To Split data based on dates [message #643794 is a reply to message #643793] |
Sun, 18 October 2015 17:21 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Thanks barbara for responding, actually i have changed my test cases , what i want to check is if bal_date is falling between start and end dates of bucket table where code starts with 3 or to be precise i will configure the codes in lookup table then make the T2 value as 200 instead of taking MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL, only if bal_date falling between the start dates of buckets.
something like this while comparing the dates i shouldnt consider the codes which is present in lookup table but for determining the T1val i should check if led_bal falling for those codes then make t1VAL as 200
SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879 and [b]code not in (select id from lookup_tab)[/b]
final output
ID BAL_DATE BAL LIMIT_AMOUT
1234 01-JAN-08 00.00.00 -195.34 -5000
1234 02-JAN-08 00.00.00 -6000 -5000
1234 03-JAN-08 00.00.00 -209.84 -25
1234 04-JAN-08 00.00.00 -54.96 -25
1234 14-OCT-09 00.00.00 -195.34 -25
1234 16-OCT-09 00.00.00 -209.84 -25
1234 15-JUN-14 00.00.00 -195.34 -5000
1234 16-JUN-14 00.00.00 -2000 -5000
1234 19-JUN-14 00.00.00 -5500 -25
bucket table
ID CODE START_DATE END_DATE PRODUCT BUK_ID
879 1490 16-NOV-07 00.00.00 09-OCT-09 00.00.00 2300 1
879 3333 20-NOV-07 00.00.00 08-OCT-08 00.00.00 2300 4
879 3334 09-OCT-08 00.00.00 08-OCT-09 00.00.00 2300 4
879 1490 10-OCT-09 00.00.00 19-NOV-10 00.00.00 2 2
879 1490 10-OCT-09 00.00.00 19-NOV-10 00.00.00 2 2
879 1490 16-JUN-14 00.00.00 20-AUG-14 00.00.00 2300 3
final output
ID_1 BAL_DATE T1 T2 T3 T4 T1PER T2PER T3PER T4PER T1_DERIVED T2_DERIVED T3_DERIVED T4_DERIVED REFAM BM BUK_ID T2MAX
1234 01-JAN-08 00.00.00 195 0 0 0 0 14.628 0 24.582 0 0.07815 0 0 -5000 2300 1 5000
1234 02-JAN-08 00.00.00 200 4800 0 1000 0 14.628 0 24.582 0 2.003836 0 0.673479 -5000 2300 1 5000
1234 03-JAN-08 00.00.00 25 0 0 185 0 14.628 0 24.582 0 0.010019 0 0.124594 -25 2300 1 5000
1234 04-JAN-08 00.00.00 25 0 0 30 0 14.628 0 24.582 0 0.010019 0 0.020204 -25 2300 1 5000
1234 14-OCT-09 00.00.00 25 0 0 170 0 14.013 0 0 0 0 0 0 -25 2 2 5000
1234 16-OCT-09 00.00.00 25 0 0 185 0 14.013 0 0 0 0 0 0 -25 2 2 5000
1234 16-JUN-14 00.00.00 15 1000 985 0 0 0.75 1.5 3 0 0.75 1.5 0 -5000 2300 3 1000
1234 19-JUN-14 00.00.00 15 1000 2000 2485 0 0.75 1.5 3 0 0.75 1.5 3 -25 2300 3 1000
updated scripts:
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('01-JAN-08 00.00.00','DD-MON-RR HH24.MI.SS'),-195.34,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('02-JAN-08 00.00.00','DD-MON-RR HH24.MI.SS'),-6000,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('03-JAN-08 00.00.00','DD-MON-RR HH24.MI.SS'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('04-JAN-08 00.00.00','DD-MON-RR HH24.MI.SS'),-54.96,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('14-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),-195.34,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('16-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),-209.84,-25);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('15-JUN-14 00.00.00','DD-MON-RR HH24.MI.SS'),-195.34,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('16-JUN-14 00.00.00','DD-MON-RR HH24.MI.SS'),-2000,-5000);
Insert into balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) values (1234,to_date('19-JUN-14 00.00.00','DD-MON-RR HH24.MI.SS'),-5500,-25);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('16-NOV-07 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('09-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',1);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3333,to_date('20-NOV-07 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',4);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3334,to_date('09-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',4);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('10-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('19-NOV-10 00.00.00','DD-MON-RR HH24.MI.SS'),'2',2);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('10-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('19-NOV-10 00.00.00','DD-MON-RR HH24.MI.SS'),'2',2);
Insert into bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',1490,to_date('16-JUN-14 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('20-AUG-14 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',3);
create table lookup_tab(id number);
insert into lookup_tab
select 3333 from dual
union all
select 3334 from dual;
query i have used
select ID_1,
BAL_DATE,
T1,
T2,
T3,
T4,
T1PER,
T2PER,
T3PER,
T4PER,
case when BAL_DATE >= date '2014-06-15' and T1 = 0 then 0 else T1_derived end T1_derived,
case when BAL_DATE >= date '2014-06-15' and T2 = 0 then 0 else T2_derived end T2_derived,
case when BAL_DATE >= date '2014-06-15' and T3 = 0 then 0 else T3_derived end T3_derived,
case when BAL_DATE >= date '2014-06-15' and T4 = 0 then 0 else T4_derived end T4_derived,
REFAM,
BM,
buk_id,
T2MAX
from (
SELECT ID_1,
BAL_DATE,
T1,
T2,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t3val, GREATEST (BAL - t1val - t2val, 0)) else 0 end T3,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t4val, GREATEST (BAL - t1val - t2val-t3val, 0)) else GREATEST(BAL -(T1 + T2),0) end T4,
T1PER,
T2PER,
T3PER,
T4PER,
case when BAL_DATE >= date '2014-06-15' then T1PER else ROUND((T1 * T1PER) /(365 * 100),6) end T1_derived,
case when BAL_DATE >= date '2014-06-15' then T2PER else ROUND((T2 * T2PER) /(365 * 100),6) end T2_derived,
case when BAL_DATE >= date '2014-06-15' then T3PER else 0 end T3_derived,
case when BAL_DATE >= date '2014-06-15' then T4PER else ROUND((GREATEST(BAL -(T1 + T2),0) * T4PER) /(365 * 100),6) end T4_derived,
REFAM,
BM,
buk_id,
T2MAX
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
T1,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t2val, GREATEST (BAL - t1val, 0)) else
GREATEST(LEAST(T2VAL,BAL - T1,DECODE(SIGN(REFAM),1,0,ABS(REFAM)) - T1),0) end T2,
T1PER,
T2PER,
T3PER,
T4PER,
BM,
buk_id,
T2MAX,t2val,T1VAL,T3VAL,T4VAL
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t1val, BAL) else
LEAST(T1VAL,BAL,DECODE(SIGN(REFAM),1,0,ABS(REFAM))) end T1,
NVL(T2VAL,0) T2VAL,
case when BAL_DATE >= date '2014-06-15' then T1CHARGE
else T1PER end T1PER,
case when BAL_DATE >= date '2014-06-15' then T2CHARGE else T2PER end T2PER,
case when BAL_DATE >= date '2014-06-15' then T3CHARGE else 0 end T3PER,
case when BAL_DATE >= date '2014-06-15' then T4CHARGE else T4PER end T4PER,
BM,
buk_id,
T2VAL T2MAX,T1VAL,T3VAL,T4VAL
FROM
(SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879 and code <>3333
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
)
)
)
)
order by BAL_DATE;
[Updated on: Sun, 18 October 2015 17:22] Report message to a moderator |
|
|
|
|
|
| Re: To Split data based on dates [message #643835 is a reply to message #643833] |
Mon, 19 October 2015 14:40 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
HI Barbara,
Please find the updated query considering below updated tables
delete from bucket where code like '3%';
bucket table:
ID CODE START_DATE END_DATE PRODUCT BUK_ID
879 1490 16-NOV-07 00.00.00 09-OCT-09 00.00.00 2300 1
879 1490 10-OCT-09 00.00.00 19-NOV-10 00.00.00 2 2
879 1490 10-OCT-09 00.00.00 19-NOV-10 00.00.00 2 2
879 1490 16-JUN-14 00.00.00 20-AUG-14 00.00.00 2300 3
adding another reference table to check if the bar_date falls between dates then make t1val as 200
ref_final_table
Insert into ref_final_table (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3333,to_date('20-NOV-07 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',4);
Insert into ref_final_table (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) values ('879',3334,to_date('09-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),'2300',4);
query i have used
select ID_1,
BAL_DATE,
T1,
T2,
T3,
T4,
T1PER,
T2PER,
T3PER,
T4PER,
case when BAL_DATE >= date '2014-06-15' and T1 = 0 then 0 else T1_derived end T1_derived,
case when BAL_DATE >= date '2014-06-15' and T2 = 0 then 0 else T2_derived end T2_derived,
case when BAL_DATE >= date '2014-06-15' and T3 = 0 then 0 else T3_derived end T3_derived,
case when BAL_DATE >= date '2014-06-15' and T4 = 0 then 0 else T4_derived end T4_derived,
REFAM,
BM,
buk_id,
T2MAX
from (
SELECT ID_1,
BAL_DATE,
T1,
T2,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t3val, GREATEST (BAL - t1val - t2val, 0)) else 0 end T3,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t4val, GREATEST (BAL - t1val - t2val-t3val, 0)) else GREATEST(BAL -(T1 + T2),0) end T4,
T1PER,
T2PER,
T3PER,
T4PER,
case when BAL_DATE >= date '2014-06-15' then T1PER else ROUND((T1 * T1PER) /(365 * 100),6) end T1_derived,
case when BAL_DATE >= date '2014-06-15' then T2PER else ROUND((T2 * T2PER) /(365 * 100),6) end T2_derived,
case when BAL_DATE >= date '2014-06-15' then T3PER else 0 end T3_derived,
case when BAL_DATE >= date '2014-06-15' then T4PER else ROUND((GREATEST(BAL -(T1 + T2),0) * T4PER) /(365 * 100),6) end T4_derived,
REFAM,
BM,
buk_id,
T2MAX
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
T1,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t2val, GREATEST (BAL - t1val, 0)) else
GREATEST(LEAST(T2VAL,BAL - T1,DECODE(SIGN(REFAM),1,0,ABS(REFAM)) - T1),0) end T2,
T1PER,
T2PER,
T3PER,
T4PER,
BM,
buk_id,
T2MAX,t2val,T1VAL,T3VAL,T4VAL
FROM
(SELECT ID_1,
BAL_DATE,
BAL,
REFAM,
case when BAL_DATE >= date '2014-06-15' then
LEAST (t1val, BAL) else
LEAST(T1VAL,BAL,DECODE(SIGN(REFAM),1,0,ABS(REFAM))) end T1,
NVL(T2VAL,0) T2VAL,
case when BAL_DATE >= date '2014-06-15' then T1CHARGE
else T1PER end T1PER,
case when BAL_DATE >= date '2014-06-15' then T2CHARGE else T2PER end T2PER,
case when BAL_DATE >= date '2014-06-15' then T3CHARGE else 0 end T3PER,
case when BAL_DATE >= date '2014-06-15' then T4CHARGE else T4PER end T4PER,
BM,
buk_id,
T2VAL T2MAX,T1VAL,T3VAL,T4VAL
FROM
(SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
)
)
)
)
order by BAL_DATE;
i am struck how i can change the T1val to 200 if bad_date falls between dates ref_final_table to get below output showing only changed values
ID_1 BAL_DATE T1 T2 T3 T4 T1PER T2PER T3PER T4PER T1_DERIVED T2_DERIVED T3_DERIVED T4_DERIVED REFAM BM BUK_ID T2MAX
1234 01-JAN-08 00.00.00 195 0 0 0 0 14.628 0 24.582 0 0.07815 0 0 -5000 2300 1 5000
1234 02-JAN-08 00.00.00 200 4800 0 1000 0 14.628 0 24.582 0 2.003836 0 0.673479 -5000 2300 1 5000
1234 03-JAN-08 00.00.00 25 0 0 185 0 14.628 0 24.582 0 0.010019 0 0.124594 -25 2300 1 5000
1234 04-JAN-08 00.00.00 25 0 0 30 0 14.628 0 24.582 0 0.010019 0 0.020204 -25 2300 1 5000
|
|
|
|
|
|
| Re: To Split data based on dates [message #643856 is a reply to message #643841] |
Tue, 20 October 2015 05:50 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi Barbara,
i need to change the t1val in below subquery when bal_date falls between dates of ref_final_table
SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
|
|
|
|
| Re: To Split data based on dates [message #643893 is a reply to message #643856] |
Tue, 20 October 2015 14:53 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please note the differences between your sub-query below and my revised sub-query below that. This uses your most recent tables and insert statements on this thread.
-- your sub-query:
SCOTT@orcl12c> SELECT TB.ID ID_1,
2 TB.BAL_DATE BAL_DATE,
3 ROUND(ABS(TB.BAL)) BAL,
4 DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
5 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
6 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
7 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
8 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
9 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
10 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
11 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
12 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
13 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
14 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
15 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
16 BKT.product BM,
17 bkt.buk_id buk_id
18 FROM BALANCE TB,
19 Reference_table REF,
20 (SELECT DISTINCT start_date,
21 end_date,
22 product, buk_id
23 FROM bucket BK
24 WHERE id = 879) BKT
25 WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
26 AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
27 AND BKT.product = REF.product
28 AND TB.ID = 1234
29 GROUP BY TB.ID,
30 TB.BAL_DATE,
31 TB.BAL,
32 TB.LIMIT_AMOUT,
33 BKT.product,
34 bkt.buk_id
35 ORDER BY bal_date
36 /
ID_1 BAL_DATE BAL REFAM T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER T1CHARGE T2CHARGE T3CHARGE T4CHARGE BM BUK_ID
---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1234 01-JAN-08 195 -5000 0 5000 0 14.628 24.582 2300 1
1234 01-JAN-08 195 -5000 0 5000 0 14.628 24.582 2300 4
1234 02-JAN-08 6000 -5000 0 5000 0 14.628 24.582 2300 1
1234 02-JAN-08 6000 -5000 0 5000 0 14.628 24.582 2300 4
1234 03-JAN-08 210 -25 0 5000 0 14.628 24.582 2300 1
1234 03-JAN-08 210 -25 0 5000 0 14.628 24.582 2300 4
1234 04-JAN-08 55 -25 0 5000 0 14.628 24.582 2300 1
1234 04-JAN-08 55 -25 0 5000 0 14.628 24.582 2300 4
1234 14-OCT-09 195 -25 250 5000 0 14.013 0 2 2
1234 16-OCT-09 210 -25 250 5000 0 14.013 0 2 2
10 rows selected.
-- revised sub-query:
SCOTT@orcl12c> SELECT TB.ID ID_1,
2 TB.BAL_DATE BAL_DATE,
3 ROUND(ABS(TB.BAL)) BAL,
4 DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
5 case when tb.bal_date between rft.start_date and rft.end_date then 200 else
6 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) end T1VAL,
7 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
8 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
9 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
10 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
11 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
12 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
13 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
14 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
15 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
16 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
17 BKT.product BM,
18 bkt.buk_id buk_id
19 FROM BALANCE TB,
20 Reference_table REF,
21 (SELECT DISTINCT id, code, start_date,
22 end_date,
23 product, buk_id
24 FROM bucket BK
25 WHERE id = 879) BKT,
26 ref_final_table rft
27 WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
28 AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
29 AND BKT.product = REF.product
30 AND TB.ID = 1234
31 and rft.id = bkt.id
32 and rft.code = bkt.code
33 GROUP BY TB.ID,
34 TB.BAL_DATE,
35 TB.BAL,
36 TB.LIMIT_AMOUT,
37 BKT.product,
38 bkt.buk_id,
39 rft.start_date,
40 rft.end_date
41 ORDER BY bal_date
42 /
ID_1 BAL_DATE BAL REFAM T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER T1CHARGE T2CHARGE T3CHARGE T4CHARGE BM BUK_ID
---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1234 01-JAN-08 195 -5000 200 5000 0 14.628 24.582 2300 4
1234 02-JAN-08 6000 -5000 200 5000 0 14.628 24.582 2300 4
1234 03-JAN-08 210 -25 200 5000 0 14.628 24.582 2300 4
1234 04-JAN-08 55 -25 200 5000 0 14.628 24.582 2300 4
4 rows selected.
|
|
|
|
| Re: To Split data based on dates [message #643914 is a reply to message #643893] |
Wed, 21 October 2015 06:05 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Thanks barabara, i think below query will work because in ref_final_table the id will be different changed the table scripts
SELECT TB.ID ID_1,
TB.BAL_DATE BAL_DATE,
ROUND(ABS(TB.BAL)) BAL,
DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
case when exists (select 1 from ref_final_table rft where tb.bal_date between rft.start_date and rft.end_date
and rft.code in (select id from lookup_tab )) then 200 else MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) end T1VAL,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
BKT.product BM,
bkt.buk_id buk_id
FROM BALANCE TB,
Reference_table REF,
(SELECT DISTINCT start_date,
end_date,
product,buk_id
FROM bucket BK
WHERE id = 879
) BKT
WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
AND BKT.product = REF.product
AND TB.ID = 1234
GROUP BY TB.ID,
TB.BAL_DATE,
TB.BAL,
TB.LIMIT_AMOUT,
BKT.product,
bkt.buk_id
order by TB.BAL_DATE;
ID_1 BAL_DATE BAL REFAM T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER T1CHARGE T2CHARGE T3CHARGE T4CHARGE BM BUK_ID
1234 01-JAN-08 00.00.00 195 -5000 200 5000 0 14.628 24.582 2300 1
1234 01-JAN-08 00.00.00 195 -5000 200 5000 0 14.628 24.582 2300 4
1234 02-JAN-08 00.00.00 6000 -5000 200 5000 0 14.628 24.582 2300 1
1234 02-JAN-08 00.00.00 6000 -5000 200 5000 0 14.628 24.582 2300 4
1234 03-JAN-08 00.00.00 210 -25 200 5000 0 14.628 24.582 2300 1
1234 03-JAN-08 00.00.00 210 -25 200 5000 0 14.628 24.582 2300 4
1234 04-JAN-08 00.00.00 55 -25 200 5000 0 14.628 24.582 2300 1
1234 04-JAN-08 00.00.00 55 -25 200 5000 0 14.628 24.582 2300 4
1234 14-OCT-09 00.00.00 195 -25 250 5000 0 14.013 0 2 2
1234 16-OCT-09 00.00.00 210 -25 250 5000 0 14.013 0 2 2
1234 16-JUN-14 00.00.00 2000 -5000 15 1000 2000 5000 0 0.75 1.5 3 2300 3
1234 19-JUN-14 00.00.00 5500 -25 15 1000 2000 5000 0 0.75 1.5 3 2300 3
Updated scripts
create table ref_final_table(id number,start_date date,end_date date,code number);
Insert into ref_final_table (ID,START_DATE,END_DATE,CODE) values (111,to_date('20-NOV-07 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),3333);
Insert into ref_final_table (ID,START_DATE,END_DATE,CODE) values (111,to_date('09-OCT-08 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('08-OCT-09 00.00.00','DD-MON-RR HH24.MI.SS'),3334);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: To Split data based on dates [message #644006 is a reply to message #644000] |
Sun, 25 October 2015 07:30 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It looks to me like your code is doing what you want. You say that you want it to replace the t1val with 200 when the bal_date is between the start_date and end_date and it is doing that. It is also applying the additional condition involving the lookup_tab. Please see the demonstration below.
-- without the modification to t1val:
SCOTT@orcl12c> SELECT TB.ID ID_1,
2 TB.BAL_DATE BAL_DATE,
3 ROUND(ABS(TB.BAL)) BAL,
4 DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
5 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) T1VAL,
6 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
7 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
8 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
9 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
10 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
11 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
12 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
13 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
14 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
15 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
16 BKT.product BM,
17 bkt.buk_id buk_id
18 FROM BALANCE TB,
19 Reference_table REF,
20 (SELECT DISTINCT start_date,
21 end_date,
22 product,buk_id
23 FROM bucket BK
24 WHERE id = 879
25 ) BKT
26 WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
27 AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
28 AND BKT.product = REF.product
29 AND TB.ID = 1234
30 GROUP BY TB.ID,
31 TB.BAL_DATE,
32 TB.BAL,
33 TB.LIMIT_AMOUT,
34 BKT.product,
35 bkt.buk_id
36 order by TB.BAL_DATE;
ID_1 BAL_DATE BAL REFAM T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER T1CHARGE T2CHARGE T3CHARGE T4CHARGE BM BUK_ID
---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1234 01-JAN-08 195 -5000 0 5000 0 14.628 24.582 2300 1
1234 01-JAN-08 195 -5000 0 5000 0 14.628 24.582 2300 4
1234 02-JAN-08 6000 -5000 0 5000 0 14.628 24.582 2300 1
1234 02-JAN-08 6000 -5000 0 5000 0 14.628 24.582 2300 4
1234 03-JAN-08 210 -25 0 5000 0 14.628 24.582 2300 1
1234 03-JAN-08 210 -25 0 5000 0 14.628 24.582 2300 4
1234 04-JAN-08 55 -25 0 5000 0 14.628 24.582 2300 1
1234 04-JAN-08 55 -25 0 5000 0 14.628 24.582 2300 4
1234 14-OCT-09 195 -25 250 5000 0 14.013 0 2 2
1234 16-OCT-09 210 -25 250 5000 0 14.013 0 2 2
10 rows selected.
-- with the modification to t1val:
SCOTT@orcl12c> SELECT TB.ID ID_1,
2 TB.BAL_DATE BAL_DATE,
3 ROUND(ABS(TB.BAL)) BAL,
4 DECODE(SIGN(TB.LIMIT_AMOUT), - 1,TB.LIMIT_AMOUT,NVL(TB.LIMIT_AMOUT,0)) REFAM,
5 case when exists (select 1 from ref_final_table rft where tb.bal_date between rft.start_date and rft.end_date
6 and rft.code in (select id from lookup_tab )) then 200 else MAX(DECODE(TRIM(REF.TYPE),'T1',REF.MAX_AMT)) end T1VAL,
7 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.MAX_AMT)) T2VAL,
8 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.MAX_AMT))T3VAL,
9 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.MAX_AMT))T4VAL,
10 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.RATE)) T1PER,
11 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.RATE)) T2PER,
12 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.RATE)) T4PER,
13 MAX(DECODE(TRIM(REF.TYPE),'T1',REF.CHARGE)) T1CHARGE,
14 MAX(DECODE(TRIM(REF.TYPE),'T2',REF.CHARGE)) T2CHARGE,
15 MAX(DECODE(TRIM(REF.TYPE),'T3',REF.CHARGE)) T3CHARGE,
16 MAX(DECODE(TRIM(REF.TYPE),'T4',REF.CHARGE)) T4CHARGE,
17 BKT.product BM,
18 bkt.buk_id buk_id
19 FROM BALANCE TB,
20 Reference_table REF,
21 (SELECT DISTINCT start_date,
22 end_date,
23 product,buk_id
24 FROM bucket BK
25 WHERE id = 879
26 ) BKT
27 WHERE TB.BAL_DATE BETWEEN REF.EFF_FROM_DATE AND REF.EFF_TO_DATE
28 AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
29 AND BKT.product = REF.product
30 AND TB.ID = 1234
31 GROUP BY TB.ID,
32 TB.BAL_DATE,
33 TB.BAL,
34 TB.LIMIT_AMOUT,
35 BKT.product,
36 bkt.buk_id
37 order by TB.BAL_DATE;
ID_1 BAL_DATE BAL REFAM T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER T1CHARGE T2CHARGE T3CHARGE T4CHARGE BM BUK_ID
---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1234 01-JAN-08 195 -5000 200 5000 0 14.628 24.582 2300 1
1234 01-JAN-08 195 -5000 200 5000 0 14.628 24.582 2300 4
1234 02-JAN-08 6000 -5000 200 5000 0 14.628 24.582 2300 1
1234 02-JAN-08 6000 -5000 200 5000 0 14.628 24.582 2300 4
1234 03-JAN-08 210 -25 200 5000 0 14.628 24.582 2300 1
1234 03-JAN-08 210 -25 200 5000 0 14.628 24.582 2300 4
1234 04-JAN-08 55 -25 200 5000 0 14.628 24.582 2300 1
1234 04-JAN-08 55 -25 200 5000 0 14.628 24.582 2300 4
1234 14-OCT-09 195 -25 250 5000 0 14.013 0 2 2
1234 16-OCT-09 210 -25 250 5000 0 14.013 0 2 2
10 rows selected.
-- values from other tables to show that all t1val that were changed to 200 above were between the dates and such:
SCOTT@orcl12c> SELECT * FROM ref_final_table
2 /
ID CODE START_DAT END_DATE PRODUCT BUK_ID
---------- ---------- --------- --------- ---------- ----------
879 3333 20-NOV-07 08-OCT-08 2300 4
879 3334 09-OCT-08 08-OCT-09 2300 4
2 rows selected.
SCOTT@orcl12c> SELECT * FROM lookup_tab
2 /
ID
----------
3333
3334
2 rows selected.
|
|
|
|
| Re: To Split data based on dates [message #644010 is a reply to message #644006] |
Sun, 25 October 2015 13:44 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
yes barbara i am able to get the results based on my updated query one more thing in main query there is change in logic for derivation when the bal_date is greater than 2014-06-15 i need to apply below logic
i.e to check the balance which falls in range from reference_table and then put the value in respective T1,T2,T3,T4
output
ID_1 BAL_DATE T1 T2 T3 T4
1234 16-JUN-14 00.00.00 0 0 2000 0
1234 19-JUN-14 00.00.00 0 0 0 0
balance
ID BAL_DATE BAL
1234 16-JUN-14 00.00.00 -2000
1234 19-JUN-14 00.00.00 -5500
reference_table
TYPE MIN_AMT MAX_AMT
T1 0 15
T2 16 1000
T3 1001 2000
T4 2001 5000
[Updated on: Sun, 25 October 2015 15:00] Report message to a moderator |
|
|
|
| Re: To Split data based on dates [message #644011 is a reply to message #644010] |
Sun, 25 October 2015 18:19 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your problem is unclear as usual. If there are any changes in your test data, then you need to post that. You need to also post what query or sub-query you have tried to apply the additional condition, what results you have gotten, and what results you want. You need to very clear as to which is which. Providing only one and labeling it as output does not say if that is what you are getting or what you want. Please bear in mind that we are not here to do your work for you. You need to try something yourself first before just posting an additional requirement. As suggested by others elsewhere, if you are having this much trouble with your whole problem, you probably need to hire a consultant. You have exceeded what can be expected from forums.
|
|
|
|
Goto Forum:
Current Time: Sun Jul 19 00:45:10 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
