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
Sum according to range and rate (0 Bytes)
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