Hello TNO members,
I have a complicated problem I need to solve, how ever I am missing knowledge about calculating using previous rows and columns in current select.
Test data
with t as (
select 1 as box, 1 as box_group, 10 as max_qty from dual union all
select 2, 1, 15 from dual union all
select 3, 1, 40 from dual union all
select 4, 1, 45 from dual union all
select 5, 2, 15 from dual union all
select 6, 2, 20 from dual union all
select 7, 2, 20 from dual union all
select 8, 3, 20 from dual)
Expected Output result
box box_group max_qty assigned_from_60
1 1 10 10
2 1 15 15
3 1 40 17
4 1 45 18
5 2 15 0
6 2 20 0
7 2 20 0
8 3 20 0
The problem:
In total 60 items are shared among the boxes in the same group, ordered by the lowest max_qty.
10 items can be assign to each box in group 1. (Used items: 40)
The remaining 20 items will be assigned to the other boxes in group 1.
5 more items can be assign to each box in group 1 (Used items: 15)
The remaining 15 items will be assigned to the remaining boxes in group 1.
2 more items can be assign to each box in group 1 (used items: 4)
One item remains. When items cannot be shared equally among the remaining boxes, ordered by the highest max_quantity, assign +1 till no item remains.
My solution in steps:
1. Calculate max_qty difference. How can I calculate the difference between the max_qty from box 1 and 2? Tricky is not to calculate the difference between different groups.
This means output result should be something like
box box_group max_qty qty_dif
1 1 10 10
2 1 15 5
3 1 40 25
4 1 45 5
5 2 15 15
6 2 20 5
7 2 20 0
8 3 20 20
2. Remaining boxes in the same group. I want to know how many boxes are in the same group. Especially the remaining boxes when the current max_quantity is filled.
Using the following code does not result in the correct output, what is wrong or missing here?
count(*) over(partition by box_group order by max_qty asc range between current row and unbounded following)
This means output result should be something like
box box_group max_qty qty_dif rem_boxes
1 1 10 10 4
2 1 15 5 3
3 1 40 25 2
4 1 45 5 1
5 2 15 15 3
6 2 20 5 2
7 2 20 0 1
8 3 20 20 1
3. Calculate costs. This one is faily easy rem_boxes * qty_dif (*per row*)
This means output result should be something like
box box_group max_qty qty_dif rem_boxes cost
1 1 10 10 4 40
2 1 15 5 3 15
3 1 40 25 2 50
4 1 45 5 1 5
5 2 15 15 3 45
6 2 20 5 2 10
7 2 20 0 1 0
8 3 20 20 1 20
4. Calculate rem_items. 60 - (rem_boxes * qty_dif of box 1) - (rem_boxes * qty_dif of box 2) - (rem_boxes * qty_dif
of box n). How can I calculate using results of previous rows? (*all, not per group*)
This means output result should be something like
box box_group max_qty qty_dif rem_boxes cost rem_items
1 1 10 10 4 40 20
2 1 15 5 3 15 5
3 1 40 25 2 50 -45
4 1 45 5 1 5 -50
5 2 15 15 3 45 -95
6 2 20 5 2 10 -105
7 2 20 0 1 0 -105
8 3 20 20 1 20 -125
5. Assign full quantity. For each row check if rem_items > 0 then 1 else 0
This means output result should be something like
box box_group max_qty qty_dif rem_boxes cost rem_items assign
1 1 10 10 4 40 20 1
2 1 15 5 3 15 5 1
3 1 40 25 2 50 -45 0
4 1 45 5 1 5 -50 0
5 2 15 15 3 45 -95 0
6 2 20 5 2 10 -105 0
7 2 20 0 1 0 -105 0
8 3 20 20 1 20 -125 0
6. Calculate assign quantity attemp 1. Calculate assign quantity of remaining boxes per group
When assign = 1 then max_qty else pervious a_qty (*within same group*)
This means output result should be something like
box box_group max_qty qty_dif rem_boxes cost rem_items assign a_qty
1 1 10 10 4 40 20 1 10
2 1 15 5 3 15 5 1 15
3 1 40 25 2 50 -45 0 15
4 1 45 5 1 5 -50 0 15
5 2 15 15 3 45 -95 0 0
6 2 20 5 2 10 -105 0 0
7 2 20 0 1 0 -105 0 0
8 3 20 20 1 20 -125 0 0
How to solve the rest, I do not know yet. Any other suggestion to solve this problem, is welcome.
Since I'm not really a professional this is what I tried till now
with z as (
select 1 as box, 1 as box_group, 10 as max_qty from dual union all
select 2, 1, 15 from dual union all
select 3, 1, 40 from dual union all
select 4, 1, 45 from dual union all
select 5, 2, 15 from dual union all
select 6, 2, 20 from dual union all
select 7, 2, 20 from dual union all
select 8, 3, 20 from dual)
select u.*,
case
when u.assign = 2 then u.max_qty
when u.assign = 1 then 0
when u.assign = 0 then 0
end as assigned_qty
from
(
select v.*,
case
when 60 - sum(v.max_qty) over (order by v.box_group, v.max_qty, v.box) >= 0
and v.rem_items_before >= 0 then 2
when 60 - sum(v.max_qty) over (order by v.box_group, v.max_qty, v.box) < 0
and v.rem_items_before > 0 then 1 else 0
end as assign
from
(
select w.*,
w.rem_items_after + w.max_qty as rem_items_before
from
(
select x.*,
60 - x.qty_assigned as rem_items_after
from
(
select y.*,
y.max_qty * y.rem_boxes as total_cost,
sum(y.max_qty) over (order by y.box_group, y.max_qty, y.box) as qty_assigned
from
(
select z.*,
count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as rem_boxes
from z
) y
) x
) w
) v
) u
Kind regards,
Metro
Edited by: 858378 on 30-mei-2011 4:39
Edited by: 858378 on 30-mei-2011 5:05