Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

reverse engineering in Sum according to range and rate

muttleychessSep 22 2021

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 
This post has been answered by mathguy on Sep 22 2021
Jump to Answer

Comments

Post Details

Added on Sep 22 2021
1 comment
127 views