SQL> drop table assignment cascade constraints purge;
Table dropped.
SQL> drop table consignment purge;
Table dropped.
SQL>
SQL> create table ASSIGNMENT (
2 ID NUMBER not null primary key,
3 VALUE NUMBER not null,
4 ROUNDING NUMBER not null
5 )
6 ;
Table created.
SQL>
SQL>
SQL>
SQL> create table CONSIGNMENT (
2 ID NUMBER not null ,
3 CONS_NUM INTEGER not null,
4 CONS_PERCENTAGE NUMBER not null,
5 CONSTRAINT fk_id
6 FOREIGN KEY (ID)
7 REFERENCES ASSIGNMENT(ID)
8 )
9 ;
Table created.
SQL>
SQL>
SQL>
SQL> insert into ASSIGNMENT values (1,60,10) ;
1 row created.
SQL> insert into ASSIGNMENT values (2,48,6) ;
1 row created.
SQL>
SQL> insert into CONSIGNMENT values(1,1,40) ;
1 row created.
SQL> insert into CONSIGNMENT values(1,2,60) ;
1 row created.
SQL>
SQL> insert into CONSIGNMENT values(2,1,30) ;
1 row created.
SQL> insert into CONSIGNMENT values(2,2,30) ;
1 row created.
SQL> insert into CONSIGNMENT values(2,3,40) ;
1 row created.
SQL>
SQL> select a.id,
2 c.cons_num,
3 a.value,
4 a.rounding,
5 c.cons_percentage,
6 a.value * c.cons_percentage / 100 raw_cut,
7 a.rounding*trunc(a.value * c.cons_percentage / 100 / a.rounding ) trunc_down,
8 row_number() over ( partition by c.id order by c.cons_num desc ) as rk
9 from ASSIGNMENT a,
10 CONSIGNMENT c
11 where a.id = c.id
12 order by id, cons_num;
ID CONS_NUM VALUE ROUNDING CONS_PERCENTAGE RAW_CUT TRUNC_DOWN RK
---------- ---------- ---------- ---------- --------------- ---------- ---------- ----------
1 1 60 10 40 24 20 2
1 2 60 10 60 36 30 1
2 1 48 6 30 14.4 12 3
2 2 48 6 30 14.4 12 2
2 3 48 6 40 19.2 18 1
5 rows selected.
So we have "raw_cut" which is a simple percentage carve up, and "trunc_down" which takes that value but rounds it (downwards) to the nearest ROUNDING amount. So that gives us *most* of our result, but for the "last" row in each consignment, I'm assuming you want to pick up *all* of the remaining VALUE. eg for ID=2, we have 12, 12 so the last value should be "24". So that is where RK will be used to know which is the "last" consignment number
SQL> select id, cons_num, value,
2 case
3 when rk = 1 then
4 value - sum(trunc_down) over ( partition by id order by cons_num rows between unbounded preceding and 1 preceding )
5 else
6 trunc_down
7 end as value_split
8 from
9 (
10 select a.id,
11 c.cons_num,
12 a.value,
13 a.rounding,
14 c.cons_percentage,
15 a.value * c.cons_percentage / 100 raw_cut,
16 a.rounding*trunc(a.value * c.cons_percentage / 100 / a.rounding ) trunc_down,
17 row_number() over ( partition by c.id order by c.cons_num desc ) as rk
18 from ASSIGNMENT a,
19 CONSIGNMENT c
20 where a.id = c.id
21 order by id, cons_num
22 )
23 /
ID CONS_NUM VALUE VALUE_SPLIT
---------- ---------- ---------- -----------
1 1 60 20
1 2 60 40
2 1 48 12
2 2 48 12
2 3 48 24
5 rows selected.
SQL>