Derivation of values falling on date ranges

rohit_shinez · February 11, 2016, 3:35pm

Hi Guys,

I am having below tables used in oracle

bal
 
ID	BALANCE	BAL_DATE
1	-11.71	01-JAN-05 00.00.00
1	-405.71	02-JAN-05 00.00.00
1	-760.71	03-JAN-05 00.00.00
 
ref_table
PRODUCT	EFF_FROM_DATE	EFF_TO_DATE	TYPE	MIN_AMT	MAX_AMT	CHARGE
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T1	0	15	0
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T2	16	1000	0.75
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T3	1001	2000	1.5
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T4	2001	5000	3
 
periods:
START_DATE	END_DATE	ID
02-JAN-05 00.00.00	13-OCT-05 00.00.00	1
 
Output required
 
 
T1_VAL	T2_VAL	T3_VAL	T4_VAL	BAL_DATE	T1	T2	T3	T4
-11.71	0	0	0	01-JAN-05 00.00.00	0	0	0	0     
-405.71	0	0	0	02-JAN-05 00.00.00	0	0	0	0     
0	-760.71	0	0	03-JAN-05 00.00.00	0	0.75	0	0     

Query i have used to modify:
 
select        SUM (CASE r.type WHEN 'T1' THEN b.balance ELSE 0 END)                AS t1_val
,         SUM (CASE r.type WHEN 'T2' THEN b.balance ELSE 0 END)                AS t2_val
,         SUM (CASE r.type WHEN 'T3' THEN b.balance ELSE 0 END)                AS t3_val
,         SUM (CASE r.type WHEN 'T4' THEN b.balance ELSE 0 END)                AS t4_val,
b.bal_date
,         MIN (CASE WHEN r.type='T1' THEN r.charge  ELSE 0 END)  AS t1
,         MIN (CASE WHEN r.type='T2' THEN r.charge  ELSE 0 END)  AS t2
,         MIN (CASE WHEN  r.type='T3' THEN r.charge  ELSE 0 END)  AS t3
,         MIN (CASE WHEN r.type='T4' THEN r.charge  ELSE 0 END)  AS t4
from bal b, reference_table r
where b.bal_date between r.eff_from_date and r.eff_to_date
and abs(round(b.balance)) between r.min_amt and r.max_amt
GROUP BY  b.id, b.bal_date
order by bal_date;

Derivation condition:

First to check bal_date falling in start and end dates of reference table taking into first record
- 01-JAN-05 falling between the dates and check balance i.e 11.71 falls between T1 i.e 0 -15 then place it at T1_val and rest T2_val,t3_val,t4_val as zero

01-JAN-05 00.00.00-11.71000
Then place the charge value in T1 - say here it will be 0 hence
1 01-JAN-05 00.00.00 -11.71 0 0 0 0 0 0 0
If there bal_date falling in periods table then adjust T1 range like 0-500 (500 will default value)and T2 range 501-1000, T3 range 1001 - 2000, T4 2001-5000

The ranges needs to be shifted like below from reference table
 
T1	0	500	0
T2	501	1000	0.75
T3	1001	2000	1.5
T4	2001	5000	3

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);

durden_tyler · February 11, 2016, 11:23pm

SQL> 
SQL> select * from bal;

	ID    BALANCE BAL_DATE
---------- ---------- ---------
	 1     -11.71 01-JAN-05
	 1    -405.71 02-JAN-05
	 1    -760.71 03-JAN-05

3 rows selected.

SQL> 
SQL> select * from reference_table;

   PRODUCT EFF_FROM_DATE   EFF_TO_DATE	   TYPE     MIN_AMT    MAX_AMT	   CHARGE
---------- --------------- --------------- ----- ---------- ---------- ----------
	12 01-JAN-05	   01-JAN-06	   T1		  0	    15		0
	12 01-JAN-05	   01-JAN-06	   T2		 16	  1000	      .75
	12 01-JAN-05	   01-JAN-06	   T3	       1001	  2000	      1.5
	12 01-JAN-05	   01-JAN-06	   T4	       2001	  5000		3

4 rows selected.

SQL> 
SQL> select * from periods;

START_DAT END_DATE	    ID
--------- --------- ----------
02-JAN-05 13-OCT-05	     1

1 row selected.

SQL> 
SQL> --
with bal_dtl as (
    select b.balance, b.bal_date,
           case when coalesce(to_char(p.id), to_char(p.start_date), to_char(p.end_date)) is null then 'N'
                else 'Y'
           end as adj_reqd
      from bal b
           left outer join periods p
           on (b.id = p.id and b.bal_date between p.start_date and p.end_date)
),
ref_bal as (
    select r.type, bd.balance, bd.bal_date, r.charge, r.min_amt, r.max_amt,
           case when bd.adj_reqd = 'Y' then 500*(row_number() over (partition by bd.bal_date order by r.type)-1) end as min_amt_adj,
           case when bd.adj_reqd = 'Y' then 500*row_number() over (partition by bd.bal_date order by r.type) end as max_amt_adj
      from reference_table r, bal_dtl bd
     where bd.bal_date between r.eff_from_date and r.eff_to_date
)
select nvl(t1_val,0) as t1_val, nvl(t2_val,0) as t2_val, nvl(t3_val,0) as t3_val, nvl(t4_val,0) as t4_val,
       bal_date,
       nvl(t1,0) as t1, nvl(t2,0) as t2, nvl(t3,0) as t3, nvl(t4,0) as t4
  from (
      select rb.type, rb.balance, rb.bal_date, rb.charge
        from ref_bal rb
       where abs(round(rb.balance)) between nvl(min_amt_adj,min_amt) and nvl(max_amt_adj,max_amt)
  )
  pivot (
      max(balance) as val, max(charge)
      for type in ('T1' as t1, 'T2' as t2, 'T3' as t3, 'T4' as t4)
  )
;

    T1_VAL     T2_VAL	  T3_VAL     T4_VAL BAL_DATE	      T1	 T2	    T3	       T4
---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
    -11.71	    0	       0	  0 01-JAN-05	       0	  0	     0		0
   -405.71	    0	       0	  0 02-JAN-05	       0	  0	     0		0
	 0    -760.71	       0	  0 03-JAN-05	       0	.75	     0		0

3 rows selected.

SQL> 
SQL>

rohit_shinez · February 13, 2016, 10:29am

Thanks can any modification done from my query which i have posted
and while taking 500 i need take greatest(500,T1 max_amt)

---------- Post updated at 04:56 PM ---------- Previous update was at 05:27 AM ----------

Hi Durden,

can you advise ???

---------- Post updated 02-13-16 at 10:29 AM ---------- Previous update was 02-12-16 at 04:56 PM ----------

durden_tyler:

SQL> 
SQL> select * from bal;

	ID    BALANCE BAL_DATE
---------- ---------- ---------
	 1     -11.71 01-JAN-05
	 1    -405.71 02-JAN-05
	 1    -760.71 03-JAN-05

3 rows selected.

SQL> 
SQL> select * from reference_table;

   PRODUCT EFF_FROM_DATE   EFF_TO_DATE	   TYPE     MIN_AMT    MAX_AMT	   CHARGE
---------- --------------- --------------- ----- ---------- ---------- ----------
	12 01-JAN-05	   01-JAN-06	   T1		  0	    15		0
	12 01-JAN-05	   01-JAN-06	   T2		 16	  1000	      .75
	12 01-JAN-05	   01-JAN-06	   T3	       1001	  2000	      1.5
	12 01-JAN-05	   01-JAN-06	   T4	       2001	  5000		3

4 rows selected.

SQL> 
SQL> select * from periods;

START_DAT END_DATE	    ID
--------- --------- ----------
02-JAN-05 13-OCT-05	     1

1 row selected.

SQL> 
SQL> --
with bal_dtl as (
   select b.balance, b.bal_date,
   case when coalesce(to_char(p.id), to_char(p.start_date), to_char(p.end_date)) is null then 'N'
   else 'Y'
   end as adj_reqd
   from bal b
   left outer join periods p
   on (b.id = p.id and b.bal_date between p.start_date and p.end_date)
),
ref_bal as (
   select r.type, bd.balance, bd.bal_date, r.charge, r.min_amt, r.max_amt,
   case when bd.adj_reqd = 'Y' then 500*(row_number() over (partition by bd.bal_date order by r.type)-1) end as min_amt_adj,
   case when bd.adj_reqd = 'Y' then 500*row_number() over (partition by bd.bal_date order by r.type) end as max_amt_adj
   from reference_table r, bal_dtl bd
   where bd.bal_date between r.eff_from_date and r.eff_to_date
)
select nvl(t1_val,0) as t1_val, nvl(t2_val,0) as t2_val, nvl(t3_val,0) as t3_val, nvl(t4_val,0) as t4_val,
   bal_date,
   nvl(t1,0) as t1, nvl(t2,0) as t2, nvl(t3,0) as t3, nvl(t4,0) as t4
  from (
   select rb.type, rb.balance, rb.bal_date, rb.charge
   from ref_bal rb
   where abs(round(rb.balance)) between nvl(min_amt_adj,min_amt) and nvl(max_amt_adj,max_amt)
  )
  pivot (
   max(balance) as val, max(charge)
   for type in ('T1' as t1, 'T2' as t2, 'T3' as t3, 'T4' as t4)
  )
;

   T1_VAL     T2_VAL	  T3_VAL     T4_VAL BAL_DATE	      T1	 T2	    T3	       T4
---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
   -11.71	    0	       0	  0 01-JAN-05	       0	  0	     0		0
   -405.71	    0	       0	  0 02-JAN-05	       0	  0	     0		0
	 0    -760.71	       0	  0 03-JAN-05	       0	.75	     0		0

3 rows selected.

SQL> 
SQL>

And moreover if i change the value of 500 to another value for eg. 200 it doesnt work

durden_tyler · February 20, 2016, 2:33am

Yes, and that's exactly what I did. I started with your query as the starting point and ended up at the posted query.

Post an example of what the output looks like for such a case. How are the ranges shifted? What are their intervals? What is the final output? You can take the example you posted earlier or a new one.

How do you determine it doesn't work?
What do you look for in the output? Or, how is the output different from what you expect?
Post an example to demonstrate the difference between what you expect and what you get.

rohit_shinez · February 20, 2016, 12:36pm

Hi,

What i need to do is if i find the bal_date i.e which falls in start and end date of periods table in this case below records fall

ID	BALANCE	BAL_DATE
1	-405.71	02-JAN-05
1	-760.71	03-JAN-05

I need to adjust the ranges in below manner if my reference table is having data like below

reference table
12	01-JAN-05	01-JAN-06	T1	0	15	0
12	01-JAN-05	01-JAN-06	T2	16	1000	0.75
12	01-JAN-05	01-JAN-06	T3	1001	2000	1.5
12	01-JAN-05	01-JAN-06	T4	2001	5000	3

PRODUCT	EFF_FROM_DATE	EFF_TO_DATE	TYPE	MIN_AMT	MAX_AMT	CHARGE
12	01-JAN-05	01-JAN-06	T1	0	500 0
12	01-JAN-05	01-JAN-06	T2	501 1000	0.75
12	01-JAN-05	01-JAN-06	T3	1001	2000	1.5
12	01-JAN-05	01-JAN-06	T4	2001	5000	3

If the reference table is like below

12	01-JAN-05	01-JAN-06	T1	0	600  0
12	01-JAN-05	01-JAN-06	T2	601  1000	0.75
12	01-JAN-05	01-JAN-06	T3	1001	2000	1.5
12	01-JAN-05	01-JAN-06	T4	2001	5000	3

Then reference table ranges should be like above considering greatest of 500 or T1 max_amt here its 600 if you say greatest (500 ,600)

And cant this be done without pivot

durden_tyler · February 29, 2016, 8:12pm

Sorry, it's still unclear. 500 was the hard-coded value. You cannot have both 500 as well as 600 in the reference_table. So even if you determine the value 600, you will have to hard-code 500 in the greatest() function.

Not clear again.
If you do not pivot, your results will not be pivoted.
But you do want your results pivoted.

rohit_shinez · March 1, 2016, 10:11am

Yes 500 is hardcoded value i need to consider greatest(500,600). Hope this clarifies.

Regarding pivot you are using pivot clause, but i would have not used in my original post