reverse engineering in Sum according to range and rate

muttleychess

Hi

I will use here the continuation of a previous topic, I saw many good solutions, but now another question has arisen, so I opened this topic

https://community.oracle.com/tech/developers/discussion/4489311/sum-according-to-range-and-rate

create table TMP_TAX
(
  id_fx    INTEGER not null,
  value_fx NUMBER(22,2),
  perc_fx  NUMBER(22,2)
);

alter table TMP_TAX
  add constraint TMP_TAX_PK primary key (ID_FX)
  using index ;
 
 
 Insert into TMP_TAX
   (ID_FX, VALUE_FX, PERC_FX)
 Values
   (0, 1903.98, 0);
Insert into TMP_TAX
   (ID_FX, VALUE_FX, PERC_FX)
 Values
   (2, 924.4, 15);
Insert into TMP_TAX
   (ID_FX, VALUE_FX, PERC_FX)
 Values
   (1, 922.67, 7.5);
Insert into TMP_TAX
   (ID_FX, VALUE_FX, PERC_FX)
 Values
   (3, 913.93, 22.5);
Insert into TMP_TAX
   (ID_FX, VALUE_FX, PERC_FX)
 Values
   (4, 0, 27.5);
COMMIT;
I would like to know if it is possible to reverse engineer a specimen
Given a net value, know what input value I have to put to get this net value

Suppose for example the Gross value is 4000
the amount of tax calculated for this would be 263.87

so the net value would be: 3736.13

because
4000 - 263.87 => 2736.13


now suppose I want the value 4000 to be net
using the solution of @Jan Gorkow
SQL> WITH
  2    dat AS (SELECT 1 AS id, 4340.48 AS valor FROM DUAL),
  3    tax
  4    AS
  5     (SELECT dat.*,
  6         tmp_tax.*,
  7          GREATEST (
  8            0,
  9            LEAST (
 10             NVL (NULLIF (tmp_tax.value_fx, 0), dat.valor),
 11              dat.valor
 12             - NVL (
 13                SUM (tmp_tax.value_fx)
 14                 OVER (
 15                   PARTITION BY dat.id
 16                   ORDER BY tmp_tax.id_fx ASC
 17                   RANGE BETWEEN UNBOUNDED PRECEDING
 18                      AND   1 PRECEDING),
 19                0)))
 20         * tmp_tax.perc_fx
 21         / 100  AS tax
 22       FROM dat CROSS JOIN tmp_tax)
 23   SELECT tax.id, tax.valor, ROUND (SUM (tax.tax), 2) AS tax
 24    FROM tax
 25  GROUP BY tax.id, tax.valor;
 
        ID      VALOR        TAX
---------- ---------- ----------
         1    4340,48     340,48

so the gross value would be 4340.48, I would like to know if there is any way to
find this raw value 

mathguy

This will be pretty easy if you reuse the view I created in the with clause in your earlier thread. Now perhaps you understand a bit better why I said that view might be better saved separately, and referenced in all other queries that need it. Or, if you need to do such reverse calculations often, perhaps the view named q below should be the "actual view" you create on your system and use in other queries.

For now I'll answer by including it again in the with clause.

Another suggestion: the tax rates should be stored as decimals, not percentages (for example 0.075 instead of 7.5). That would simplify many computations. The numbers may still be presented as percentages on reports, but the data stored in the table doesn't have to be 7.5, it can very well be 0.075.

Table for testing (shows net after-tax amounts of 1000, 2000, ... , 6000):

create table net_amounts (net_val) as
  select level * 1000
  from   dual
  connect by level <= 6
;

Query and output:

with
  p (val_lb, val_ub, prev_step_tax, perc_fx) as (
    select nvl(sum(value_fx) over (order by id_fx
                   rows between unbounded preceding and 1 preceding), 0),
           case when value_fx != 0 then sum(value_fx) over (order by id_fx) end,
           nvl(sum(value_fx * perc_fx) over (order by id_fx
                   rows between unbounded preceding and 1 preceding) / 100, 0),
           perc_fx
    from   tmp_tax
  )
, q (val_lb, val_ub, prev_step_tax, perc_fx, net_amount_lb, net_amount_ub) as (
    select val_lb, val_ub, prev_step_tax, perc_fx, val_lb - prev_step_tax,
           val_ub - lead(prev_step_tax) over (order by val_lb)
    from   p
  )
select na.net_val,
       round(q.val_lb + (na.net_val - q.net_amount_lb) / (1 - perc_fx/100), 2) as gross_val
from   net_amounts na join q on na.net_val >= q.net_amount_lb
                            and lnnvl(na.net_val >= q.net_amount_ub)
;

   NET_VAL GROSS_VAL
---------- ---------
      1000   1000.00
      2000   2007.79
      3000   3112.00
      4000   4340.48
      5000   5697.41
      6000   7076.72

mathguy

This will be pretty easy if you reuse the view I created in the with clause in your earlier thread. Now perhaps you understand a bit better why I said that view might be better saved separately, and referenced in all other queries that need it. Or, if you need to do such reverse calculations often, perhaps the view named q below should be the "actual view" you create on your system and use in other queries.

For now I'll answer by including it again in the with clause.

Another suggestion: the tax rates should be stored as decimals, not percentages (for example 0.075 instead of 7.5). That would simplify many computations. The numbers may still be presented as percentages on reports, but the data stored in the table doesn't have to be 7.5, it can very well be 0.075.

Table for testing (shows net after-tax amounts of 1000, 2000, ... , 6000):

create table net_amounts (net_val) as
  select level * 1000
  from   dual
  connect by level <= 6
;

Query and output:

with
  p (val_lb, val_ub, prev_step_tax, perc_fx) as (
    select nvl(sum(value_fx) over (order by id_fx
                   rows between unbounded preceding and 1 preceding), 0),
           case when value_fx != 0 then sum(value_fx) over (order by id_fx) end,
           nvl(sum(value_fx * perc_fx) over (order by id_fx
                   rows between unbounded preceding and 1 preceding) / 100, 0),
           perc_fx
    from   tmp_tax
  )
, q (val_lb, val_ub, prev_step_tax, perc_fx, net_amount_lb, net_amount_ub) as (
    select val_lb, val_ub, prev_step_tax, perc_fx, val_lb - prev_step_tax,
           val_ub - lead(prev_step_tax) over (order by val_lb)
    from   p
  )
select na.net_val,
       round(q.val_lb + (na.net_val - q.net_amount_lb) / (1 - perc_fx/100), 2) as gross_val
from   net_amounts na join q on na.net_val >= q.net_amount_lb
                            and lnnvl(na.net_val >= q.net_amount_ub)
;

   NET_VAL GROSS_VAL
---------- ---------
      1000   1000.00
      2000   2007.79
      3000   3112.00
      4000   4340.48
      5000   5697.41
      6000   7076.72