Because of the large decimal places in my percent column when I try to simply multiply the percent times the amount paid the net result ends up with an extra penny. I'm trying to write a query to keep track for the subtraction in a separate column. In that way I can simply distribute the difference to the last amount. Here is what I have so far.
CREATE TABLE MY_TEST
( USER_ID VARCHAR2(600 BYTE),
USER_DETAIL_CODE VARCHAR2(100 BYTE),
DETAIL_CODE VARCHAR2(4 CHAR) NOT NULL ENABLE,
PERCENT NUMBER(8,5),
USER_AMOUNT_PAID NUMBER,
WORKING_PERCENT NUMBER
)
Insert statement.
REM INSERTING into MY_TEST
SET DEFINE OFF;
Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',7.14,0.0714,150);
Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',10.47,0.1047,150);
Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',62.39,0.6239,150);
Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',20,0.2,150);
.
USER_ID | USER_DETAIL_CODE | DETAIL_CODE | PERCENT | USER_AMOUNT_PAID | WORKING_PERCENT | PAID_TIMES_PERCENT | RUNNING_SUBTRACTION |
POP | F821 | F821 | 7.14 | 150 | 0.0714 | 10.71 | 139.29 |
POP | F821 | F821 | 10.47 | 150 | 0.1047 | 15.71 | 123.58 |
POP | F821 | F821 | 62.39 | 150 | 0.6239 | 93.59 | 29.99 |
POP | F821 | F821 | 20 | 150 | 0.2 | 30 | -0.01 |
the running subtraction column is simply subtracting (10.71 - 150) which equals 123.58 than (139.29 -15.71) than (123.58 -93.59)
my goal is to essentially replace the 30 with 29.99 since otherwise my summation is of by .01 .
SELECT
user_id,
user_detail_code,
detail_code,
percent,
user_amount_paid,
working_percent,
round((working_percent * user_amount_paid),2) paid_times_percent,
''running_subtraction
FROM
my_test;
I've written the above query but I just can't seem to come up with the right syntax for the running subtraction.
Any help would be appreciated.