CODEWITHSUNDEEP
sqloracle-database
lexcro
lexcro

Reputation: 3

Splitting orders with SQL query

SQL beginner here.

Since my problem is kinda hard to explain I will try to illustrate it with Excel:

enter image description here

There are some suppliers, each with a different supplier-number who deliver a certain weight. Since a truck can only transport exactly 24 tons I'd like to have table with the orders splitted by the value of 24. So 30 tons would mean 2 different orders one with 24 tons and one with 6 tons.

Is there any way you know of to solve this problem with a SQL query?

Thanks for any help!

Upvotes: 0

Views: 94

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269983

You can do this with a numbers table. Let me assume that you have one:

select t.*, n.n,
       (case when n.n * 24 <= t.tons then 24
             else mod(t.tons, 24)
        end)
from t join
     numbers n
     on (n.n - 1) * 24 < t.tons;

Here is one simple way to generate a numbers table, if your table is large enough:

with numbers as (
      select rownum as n
      from t
     )

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191295

You could use recursive subquery factoring (assuming you're on 11gR2 or higher):

with r (supplier_number, order_weight, truck_number, truck_weight, remaining_weight) as (
  select supplier_number, order_weight, 1,
    least(order_weight, 24), order_weight - 24
  from t
  union all
  select supplier_number, order_weight, truck_number + 1,
    least(remaining_weight, 24), remaining_weight - 24
  from r
  where remaining_weight > 0
)
select supplier_number, order_weight, truck_number, truck_weight
from r
order by supplier_number, truck_number;

SUPPLIER_NUMBER ORDER_WEIGHT TRUCK_NUMBER TRUCK_WEIGHT
--------------- ------------ ------------ ------------
              1           10            1           10
              2           25            1           24
              2           25            2            1
              3           88            1           24
              3           88            2           24
              3           88            3           24
              3           88            4           16

The anchor member gets the original weight, or 24 if it's higher than that, using the least() function - and that is what is in truck number 1; and also works out what's left (which may be negative here, since it doesn't matter). The recursive member repeats that calculation on the remainder from the previous level, if it is greater than zero.

Upvotes: 1

user5683823
user5683823

Reputation:

Solution using recursive factored subquery:

with 
     inputs ( supplier_number, order_weight ) as (
       select  1, 10 from dual union all
       select  2, 25 from dual union all
       select  3, 88 from dual
     ),
     r ( supplier_number, shipment_number, order_weight, weight_remaining ) as (
       select  supplier_number, 0, null, order_weight 
         from  inputs
       union all
       select  supplier_number, shipment_number + 1, least(24, weight_remaining),
               greatest(0, weight_remaining - 24)
         from  r
         where weight_remaining != 0
     )
select   supplier_number, shipment_number, order_weight
from     r
where    shipment_number > 0
order by supplier_number, shipment_number
;




SUPPLIER_NUMBER SHIPMENT_NUMBER ORDER_WEIGHT
--------------- --------------- ------------
              1               1           10
              2               1           24
              2               2            1
              3               1           24
              3               2           24
              3               3           24
              3               4           16

Upvotes: 1

Related Questions