## Forum Stats

• 3,757,494 Users
• 2,251,236 Discussions

Discussions

# reverse engineering in Sum according to range and rate

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

```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
```

Tagged:

• Member Posts: 10,069 Blue Diamond
edited Sep 22, 2021 8:29PM Accepted Answer

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
```

• Member Posts: 10,069 Blue Diamond
edited Sep 22, 2021 8:29PM Accepted Answer

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
```