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>