Skip to Main Content
  • Questions
  • Split a int value in multiple rows based on percentage and in multiple of another number

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vivek.

Asked: June 24, 2016 - 5:35 pm UTC

Answered by: Connor McDonald - Last updated: June 30, 2016 - 1:36 am UTC

Category: SQL*Plus - Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have situation where i have to divide a integer in multiple rows depends on the %value given and the rounding specified.
The value in ASSIGNMENT table needs to be divided in multiple of ROUNDING depends on the CONSIGNMENT records.

In below data, 1 Value needs to be divided in 2 consignments and 2nd value needs to be divided in 3 consignments.
I output I am expecting for this data as

for first Record:
ID CONS_NUM CONS_PERCENTAGE Factored_Value
1 1 40 10
1 2 60 50

for second record
2 1 30 12
2 2 30 12
2 3 40 24


create table ASSIGNMENT (
ID NUMBER not null primary key,
VALUE NUMBER not null,
ROUNDING NUMBER not null
)
;

create table CONSIGNMENT (
ID NUMBER not null ,
CONS_NUM INTEGER not null,
CONS_PERCENTAGE NUMBER not null,
CONSTRAINT fk_id
FOREIGN KEY (ID)
REFERENCES ASSIGNMENT(ID)
)
;

insert into ibm4983.ASSIGNMENT values (1,60,10) ;
insert into ibm4983.ASSIGNMENT values (2,48,6) ;


insert into ibm4983.CONSIGNMENT values(1,1,40) ;
insert into ibm4983.CONSIGNMENT values(1,2,60) ;

insert into ibm4983.CONSIGNMENT values(2,1,30) ;
insert into ibm4983.CONSIGNMENT values(2,2,30) ;
insert into ibm4983.CONSIGNMENT values(2,3,40) ;

Thanks for looking into it.

and we said...


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>


and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

June 29, 2016 - 5:32 pm UTC

Reviewer: A reader

Thanks for solution.
Chris Saxon

Followup  

June 30, 2016 - 1:36 am UTC

glad we could help

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.