Calculate using previous column and rows

861381

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

Frank Kulash

Hi,

Thanks for inlcuding the sample data in a useful form.
Don't forget to say which version of Oracle you're using. The solution below works in Oracle 9 (and up), but there might be a simpler and/or more efficient solution using MODEL (introduced in Oracle 10) or recursive WITH clauses (new in Oracle 11.2).

Let's make sure I understand the problem.
You have a given number if items (:total_items = 60 in the example you gave), and you want to distribute them over the rows in a given box_group (:target_box_group=1 in this example) as evenly as possible, such that no box is assigned more than its max_qty.
Rows that do not have box_group = :target_box_group play no real role in this problem, but they have to be included in the output.
Is that right?

If so, here's one way:

VARIABLE  target_box_group	NUMBER
VARIABLE  total_items		NUMBER

EXEC  :target_box_group :=  1;
EXEC  :total_items      := 60;

	
WITH	cntr	AS
(
	SELECT	LEVEL	AS n
	FROM	(  SELECT  MAX (max_qty)	AS max_max_qty
		   FROM    t
		   WHERE   box_group	= :target_box_group
		)
	CONNECT BY  LEVEL  <= max_max_qty
)
,	got_r_num	AS
(
	SELECT	t.box
	,	c.n
	,	ROW_NUMBER () OVER ( ORDER BY  c.n
				     ,         t.max_qty	DESC
				   )	AS r_num
	FROM	cntr	c
	JOIN	t		ON c.n	<= t.max_qty
	WHERE	t.box_group	= :target_box_group
)
,	got_assigned	AS
(
	SELECT	  box
	,	  COUNT (*)	AS assigned
	FROM	  got_r_num
	WHERE	  r_num	<= :total_items
	GROUP BY  box
)
SELECT	t.*
,	NVL (a.assigned, 0)	AS assigned
FROM		t
LEFT OUTER JOIN	got_assigned	a  ON	t.box	= a.box
ORDER BY	t.box_group
,		t.max_qty
;

What results would you want if :total_items is greater than the sum of all the max_qtys in the given box_group? The query above would leave some items unassigned.

861381

Thanks,

I am using version 10g.

The number of items is parameter.
The box group is not a parameter.

You are correct about the number of items, which is an input parameter. However, the box group number is not an input parameter.
I need to excercise with partitioning, this is one of the goals of the excercise.

A number of items is given, meaning that it does not matter whether I have 60, 100 or 1000 items. The boxes with the lowest group number are filled with items first.
A box cannot exceed its maximum quantity. All items need to be distributed evenly among the boxes in the same group.
I think you understand the main problem correctly.

When total items > sum(max_qty) of group, then do the same for the next group.

I have another silly question

with t as (
select 1 as box from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual union all
select 7 from dual union all
select 8 from dual 
) 

select v.*
from
(
select t.box, sum(t.box) over (order by t.box) as summed
from t
where t.box <> 4
order by t.box) v

I still want to show box 4. With result 6, because 1+2+3 = 6. How can I do this?

Kind regards,

Metro

Edited by: 858378 on 30-mei-2011 7:46

Frank Kulash

858378 wrote:
Thanks,

I am using version 10g.

The number of items is parameter.
The box group is not a parameter.

You are correct about the number of items, which is an input parameter. However, the box group number is not an input parameter.
...
A number of items is given, meaning that it does not matter whether I have 60, 100 or 1000 items. The boxes with the lowest group number are filled with items first.
... All items need to be distributed evenly among the boxes in the same group.

So you want to fill the lowest box_group first, as evenly as possible.
If the lowest box_group isn't big enough, then you want to fill it completely, and try to put the rest into the next box_group. Is that it?
If so, you just have to remove the WHERE clauses that reference a particular box_group, and add box-group to the analytic ORDER BY clause:

WITH	cntr	AS
(
	SELECT	LEVEL	AS n
	FROM	(  SELECT  MAX (max_qty)	AS max_max_qty
		   FROM    t
		   WHERE   box_group	= :target_box_group
		)
	CONNECT BY  LEVEL  <= max_max_qty
)
,	got_r_num	AS
(
	SELECT	t.box
	,	c.n
	,	ROW_NUMBER () OVER ( ORDER BY  c.n
				     ,         t.max_qty	DESC
				   )	AS r_num
	FROM	cntr	c
	JOIN	t		ON c.n	<= t.max_qty
	WHERE	t.box_group	= :target_box_group
)
,	got_assigned	AS
(
	SELECT	  box
	,	  COUNT (*)	AS assigned
	FROM	  got_r_num
	WHERE	  r_num	<= :total_items
	GROUP BY  box
)
SELECT	t.*,	NVL (a.assigned, 0)	AS assigned
FROM		t
LEFT OUTER JOIN	got_assigned	a  ON	t.box	= a.box
ORDER BY	t.box_group
,		t.max_qty
;

I need to excercise with partitioning, this is one of the goals of the excercise.

Are you saying this is part of a homework assignment? Post the complete assignment, and give some context about what you covered in the most recent unit before the assignment was given.

This doesn't seem to be a good problem for partitioning. PARTITION BY, in analytic fucntions, is used when you want to do the same thing to different groups independently. That's not the case here. How you fill box_group=n is dependent on whether or not you filled box_groups 1, 2, 3, ..., n-1. Part of learning about a technique (such as PARTITION BY) is learning when to use it. The lessone here is that this is not an appropriate place to use that technique.

I have another silly question

If it's a separate question, even if it uses the same table, then it's better to start a separate thread.

with t as (
select 1 as box from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual union all
select 7 from dual union all
select 8 from dual 
) 

select v.*
from
(
select t.box, sum(t.box) over (order by t.box) as summed
from t
where t.box <> 4
order by t.box) v

I still want to show box 4. With result 6, because 1+2+3 = 6. How can I do this?

Do you want to the rows where box < 4? How about the rows with box > 4? What will the summed column contain for them? Always post the results you want.
Perhaps you want something like this:

SELECT	t.*
,	SUM (box) OVER ( ORDER BY      box
			 ROWS BETWEEN  UNBOUNDED PRECEDING
			      AND      1	 PRECEDING
		       )	AS summed
FROM    t;

Edited by: Frank Kulash on May 31, 2011 9:44 AM
Sorry! I posted a duplicate of my original query. See my next message below for the revised query.

861381

Hello, thanks for your help so far.

The course I am in, teaches you how to use the basic pl sql language such are selecting from tables.
I have learned things about
- SELECT
- FROM
- WHERE
- GROUP BY
- ORDER BY
- SUB SELECTION IN SELECT
- SUB SELECTION IN FROM
- SUM, COUNT, MIN, MAX
- CASES
- INNER, OUTER, LEFT, FULL, CROSS JOINS

We are now at partitioning.

It's written in Dutch, I tried to translate it, so it might not be well written English.

*Excercise 192*
Distribution center 'One by one' wants to automate the distribution process.
...
Items are distributed to the boxes one by one. 

It is similar to the next excercise, but this above was a lot more straight to the point.

*Excercise 193*

Distribution center 'All in one box' wants to automate the distribution process. 
One of the major changes in this process is to distribute items equally to all boxes in the same group. 
This means starting from the lowest quantity, assign the lowest quantity to all boxes in the same group if possible. 
If this is not possible distribute the amount of items divided by the number of boxes in the same group. If the amount of items per box is lower than 1 and not 0.
Divide the remaining items per box ordered by the box with the highest quantity, till there are no items left. 
When it is possible to distribute the lowest quantity to all boxes, move up to the next box in the same group. 
When all boxes in the same group are filled to their maximum quantity, move up to the next group and repeat this process.

The following information is available

box box_group max_qty 
1   1         10        
2   2         15      
3   1         40      
4   1         45      
5   1         15      
6   3         20      
7   2         20      
8   2         20      

A) Order the following information by box_group and max_qty as described.
Calculate the distribution results for 60, 120, 170 items.

When I have 60 items, the following output result should be

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

Box 1 and 2 can be filled completely as you can fill atleast 10 to box 1,2,3,4 and an additional 5 to box 2, 3 and 4.
The last item goes to the box 4.

when I have 120 items

box box_group max_qty assigned_from_120
1   1         10      10   
2   1         15      15
3   1         40      40
4   1         45      45
5   2         15      3
6   2         20      3
7   2         20      4
8   3         20      0

when I have 170 items

box box_group max_qty assigned_from_170
1   1         10      10   
2   1         15      15
3   1         40      40
4   1         45      45
5   2         15      15
6   2         20      20
7   2         20      20
8   3         20      5

C) Sum the maximum quantities per group
D) Get the amount of boxes in each group
E) Create a column for remaining boxes per group
F) For each distinct quantity, count the amount of boxes with this quantity
G) Calculate how many items are required to fill all boxes in the same group
H) Create a plan how to solve this distribution problem described in the introduction?
I) Solve this problem using your plan.

Is this problem solvable? I still haven't figured out how to do it.


About the sum question, I created a new thread and it is solved. But thanks for your help.

Kind regards,

Metro

Edited by: 858378 on 31-mei-2011 1:37

Aketi Jyuuzou

create table testT(box,box_group,max_qty) as
select 1,1,10 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;

Sometimes,SQL is not effective to solve business problems.
Therefore I recommend to use TableFunction.

create or replace type Print347Type as object(
box              number(2),
box_group        number(2),
max_qty          number(2),
assigned_from_60 number(2));
/

create or replace type Print347TypeSet as table of Print347Type;
/

create or replace function PrintR return Print347TypeSet PipeLined IS
    outR Print347Type := Print347Type(NULL,NULL,NULL,NULL);
    cursor cur is select box,box_group,max_qty,0 as assigned_from_60
                  from testT order by max_qty desc;
    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;

    assignValue pls_Integer :=60;
begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;

    while (assignValue > 0) loop
        for I in 1..saveData.Last Loop
            if saveData(I).box_group = 1 then
                if saveData(I).assigned_from_60 < saveData(I).max_qty then
                    saveData(I).assigned_from_60 := saveData(I).assigned_from_60+1;
                    assignValue := assignValue-1;
                    exit when not (assignValue > 0);
                end if;
            end if;
        end Loop;
    end Loop;

    for I in 1..saveData.Last Loop
        outR.box              := saveData(I).box;
        outR.box_group        := saveData(I).box_group;
        outR.max_qty          := saveData(I).max_qty;
        outR.assigned_from_60 := saveData(I).assigned_from_60;
        pipe row(outR);
    end Loop;
end;
/

select * from table(PrintR)
order by box_group,box;

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

Frank Kulash

Hi, Metro,

858378 wrote:
Hello, thanks for your help so far.

The course I am in, teaches you how to use the basic pl sql language such are selecting from tables.

Is it about PL/SQL or SQL?

I have learned things about
- SELECT
- FROM
- WHERE
- GROUP BY
- ORDER BY
- SUB SELECTION IN SELECT
- SUB SELECTION IN FROM
- SUM, COUNT, MIN, MAX
- CASES
- INNER, OUTER, LEFT, FULL, CROSS JOINS

All of these are parts of the SQL language, not PL/SQL.

We are now at partitioning.

Are you specifically at partitioning, or iare you at a point where the book talks about analytic functions, which sometimes, but not always, have a PARTTION BY clause?

It's written in Dutch, I tried to translate it, so it might not be well written English.

Excercise 192
Distribution center 'One by one' wants to automate the distribution process.
...
Items are distributed to the boxes one by one.

Sorry, I can't figure out what Exercise 192 is, based on just that.

It is similar to the next excercise, but this above was a lot more straight to the point.

*Excercise 193*

Distribution center 'All in one box' wants to automate the distribution process. 
One of the major changes in this process is to distribute items equally to all boxes in the same group. 
This means starting from the lowest quantity, assign the lowest quantity to all boxes in the same group if possible. 
If this is not possible distribute the amount of items divided by the number of boxes in the same group. If the amount of items per box is lower than 1 and not 0.
Divide the remaining items per box ordered by the box with the highest quantity, till there are no items left. 
When it is possible to distribute the lowest quantity to all boxes, move up to the next box in the same group. 
When all boxes in the same group are filled to their maximum quantity, move up to the next group and repeat this process. ...

So Exercise 193 is what you asked yesterday, right?

A) Order the following information by box_group and max_qty as described.
Calculate the distribution results for 60, 120, 170 items.

When I have 60 items, the following output result should be

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

Box 1 and 2 can be filled completely as you can fill atleast 10 to box 1,2,3,4 and an additional 5 to box 2, 3 and 4.
The last item goes to the box 4.

when I have 120 items

box box_group max_qty assigned_from_120
1   1         10      10   
2   1         15      15
3   1         40      40
4   1         45      45
5   2         15      3
6   2         20      3
7   2         20      4
8   3         20      0

Based on what you posted, it seems like the following should be equally acceptable:

box box_group max_qty assigned_from_120
1   1         10      10   
2   1         15      15
3   1         40      40
4   1         45      45
5   2         15      3
6   2         20      4
7   2         20      3
8   3         20      0

That is, the last 10 items have to be distributed among the 3 boxes in box_group=2 as equally as possible. So one box will get 4 items and the others will get 3. The extra item will go to the box with the highest max_qty, but in this case, there is a tie: box 6 has just as much of a claim to having the highest max_qty as box 7. The line marked "***** Add if needed *****" in the query blow guarantees that, in case of a tie like this, the box with the higher box value will be considered "larger" than another box with the same max_qty.

when I have 170 items

box box_group max_qty assigned_from_170
1   1         10      10   
2   1         15      15
3   1         40      40
4   1         45      45
5   2         15      15
6   2         20      20
7   2         20      20
8   3         20      5

I accidentally posted the wrong query yesterday. This is what I should have posted:

 WITH	cntr	AS
(
	SELECT	LEVEL	AS n
	FROM	(  SELECT  MAX (max_qty)	AS max_max_qty
		   FROM    z
--		   WHERE   box_group	= :target_box_group		-- *****  Removed  *****
		)
	CONNECT BY  LEVEL  <= max_max_qty
)
,	got_r_num	AS
(
	SELECT	z.box
	,	c.n
	,	ROW_NUMBER () OVER ( ORDER BY  box_group		-- *****  Added  *****
			      	     ,	       c.n
				     ,         z.max_qty	DESC
				     ,	       box		DESC	-- ***** Add if needed  *****
				   )	AS r_num
	FROM	cntr	c
	JOIN	z		ON c.n	<= z.max_qty
--	WHERE	z.box_group	= :target_box_group			-- *****  Removed  *****
)
,	got_assigned	AS
(
	SELECT	  box
	,	  COUNT (*)	AS assigned
	FROM	  got_r_num
	WHERE	  r_num	<= :total_items
	GROUP BY  box
)
SELECT	z.*,	NVL (a.assigned, 0)	AS assigned
FROM		z
LEFT OUTER JOIN	got_assigned	a  ON	z.box	= a.box
ORDER BY	z.box_group
,		z.max_qty
;

Yesterday, I described how you need to remove 2 lines and add 1, but the code I posted was the unchanged query. The query above is what I should have posted then. Look for comments beginning "*****" above for the changes. I apologize for my mistake.
This query gets the results you posted for all 3 values of :total_items that you posted. If it doesn't work for some other value, or some other data, post the new values and the correct results you want from them, and point out where the query above is wrong.

C) Sum the maximum quantities per group
D) Get the amount of boxes in each group
E) Create a column for remaining boxes per group
F) For each distinct quantity, count the amount of boxes with this quantity
G) Calculate how many items are required to fill all boxes in the same group
H) Create a plan how to solve this distribution problem described in the introduction?
I) Solve this problem using your plan.

Are these the steps that the book suggests using?
I don't understand that approach. It might be a good way to solve the problem without using a computer. It might be a good way to solve the problem using a procedural language, such as PL/SQL. It might be one way of solving the problem in SQL, but I think it will be more complicated and less efficient than what I psoted.
The approach above is iterative; that is, you repeat certain steps, with different values. For example, you distribute a certain number of items to all boxes in a box_group. The you remove the smallest box(es) from the group, and repeat, distributing the remaining items among the remianing boxes. That's not hard to do in a language like PL/SQL, where you have loops and variables. In SQL, the closest thing to that is the MODEL clause. I'm sure you could write a MODEL solution to this problem, but, if your book hasn't mentioned MODEL yet, then that's certainly not what it's expecting you to do.

Even using the approach in steps A) trhough G) above, I don't see how a PARTITION BY would help.

Aketi Jyuuzou

There are part2 which can take parameter.

create or replace type Print347Type_part2 as object(
box             number(2),
box_group       number(2),
max_qty         number(2),
assigned_fromXX number(2));
/

create or replace type Print347TypeSet_part2 as table of Print347Type_part2;
/

create or replace function PrintR_part2(hiki_assignValue number)
return Print347TypeSet_part2 PipeLined IS
    outR Print347Type_part2 := Print347Type_part2(NULL,NULL,NULL,NULL);
    cursor cur is select box,box_group,max_qty,0 as assigned_fromXX,
                  max(box_group) over() as maxBoxGroup
                  from testT order by box_group,max_qty desc;
    type saveDataDef is table of cur%rowType index by binary_integer;
    SD saveDataDef;

    TargetBoxGroup  number :=1;
    IsAssigned      boolean;
    assignValue     number:=hiki_assignValue;
begin
    open cur;
    fetch cur bulk collect into SD;
    close cur;

    while (assignValue > 0) loop
        IsAssigned := false;
        for I in 1..SD.Last Loop
            if SD(I).box_group = TargetBoxGroup then
                if SD(I).assigned_fromXX < SD(I).max_qty then
                    SD(I).assigned_fromXX := SD(I).assigned_fromXX+1;
                    assignValue := assignValue-1;
                    exit when not (assignValue > 0);
                    IsAssigned := true;
                end if;
            end if;
        end Loop;
        if IsAssigned=false then TargetBoxGroup:= TargetBoxGroup+1; end if;
        exit when SD(1).maxBoxGroup < TargetBoxGroup;
    end Loop;

    for I in 1..SD.Last Loop
        outR.box             := SD(I).box;
        outR.box_group       := SD(I).box_group;
        outR.max_qty         := SD(I).max_qty;
        outR.assigned_fromXX := SD(I).assigned_fromXX;
        pipe row(outR);
    end Loop;
end;
/ 

sho err

select * from table(PrintR_part2(120))
order by box_group,box;

BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
---  ---------  -------  ---------------
  1          1       10               10
  2          1       15               15
  3          1       40               40
  4          1       45               45
  5          2       15                3
  6          2       20                4
  7          2       20                3
  8          3       20                0

select * from table(PrintR_part2(170))
order by box_group,box;

BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
---  ---------  -------  ---------------
  1          1       10               10
  2          1       15               15
  3          1       40               40
  4          1       45               45
  5          2       15               15
  6          2       20               20
  7          2       20               20
  8          3       20                5

select * from table(PrintR_part2(999))
order by box_group,box;

BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
---  ---------  -------  ---------------
  1          1       10               10
  2          1       15               15
  3          1       40               40
  4          1       45               45
  5          2       15               15
  6          2       20               20
  7          2       20               20
  8          3       20               20

861381

Actually it's not a book. It's excercises from college.

I think the teacher just created those herself. The teacher mentioned about partitioning as the excercise is under the topic, partitioning.
Excercise 192 is excercise 193 in an easier form, because you fill the boxes one by one and not sharing equally.
My teacher told me, that indeed a model or table function would solve the problem. So both great job

How ever, this could be done with just using some queries, she says.
And I believe XD

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 x.*,
       case when x.state = 2 then x.max_qty
            when x.state = 1 then floor(x.rem_qty/x.rem_boxes)
            else 0
       end as assign
from  (select y.*,
              count(case when y.dis_qty_after <= 60 then 1 else 0 end) over (partition by y.box_group order by y.max_qty, y.box asc range between current row and unbounded following) as rem_boxes,
              60 - sum(case when y.dis_qty_after <= 60 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
              min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
              max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
              case 
                when y.dis_qty_after <= 60 then 2
                else 
                  case
                    when min(y.dis_qty_before) over (partition by y.box_group) < 60
                    and  max(y.dis_qty_after) over (partition by y.box_group) >= 60
                    then 1 
                    else 0 
                  end
              end as state    
       from  (select z.*,
              sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
              sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after
              from   z
             ) y
      ) x

This is how much I have. I'm failing at calculating row numbers remaining with a count condition. Partitioning can be used to calculate for certain columns. But indeed, I think partition has no major influence in this excercise.

Frank Kulash

Hi,

858378 wrote:
... How ever, this could be done with just using some queries, she says.

I agree. In my last message, I posted a query that does it, without using PL/SQL or MODEL.

And I believe XD

I'm sorry, I don't understand. What does "XD" mean here?
>

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 x.*,
case when x.state = 2 then x.max_qty
when x.state = 1 then floor(x.rem_qty/x.rem_boxes)
else 0
end as assign
from  (select y.*,
count(case when y.dis_qty_after <= 60 then 1 else 0 end) over (partition by y.box_group order by y.max_qty, y.box asc range between current row and unbounded following) as rem_boxes,
60 - sum(case when y.dis_qty_after <= 60 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
case 
when y.dis_qty_after <= 60 then 2
else 
case
when min(y.dis_qty_before) over (partition by y.box_group) < 60
and  max(y.dis_qty_after) over (partition by y.box_group) >= 60
then 1 
else 0 
end
end as state    
from  (select z.*,
sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after
from   z
) y
) x

This is how much I have. I'm failing at calculating row numbers remaining with a count condition. Partitioning can be used to calculate for certain columns. But indeed, I think partition has no major influence in this excercise.

Whenever you have a problem, post the sample data (as you did), the results you want from that data, and an explanation of how you get those results from that data. I'm sure you have a clear idea of what this much of the query is supposed to do, but, to me, it is a riddle, wrapped in a mystery, inside an enigma.

861381

Thanks for helping 'XD' is a smiley.

I got the solution now. I took some time, and some input of you .

I agree, I did not describe well enough, but sometimes it is really hard to explain what you really need.
I will try to do better next time.

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 6, 2, 15 from dual union all
  select 7, 2, 20 from dual union all
  select 8, 2, 20 from dual union all
  select 9, 3, 20 from dual)

select x.*,
       case when x.state = 2 then x.max_qty
            when x.state = 1 then 
                               case when floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes <> x.rem_qty 
                                 then floor(x.rem_qty/x.sum_i_boxes) + case 
                                                                       when x.rem_boxes <= x.rem_qty - floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes then 1 else 0 end
                                 else floor(x.rem_qty/x.sum_i_boxes)
                               end
            else 0
       end as assign
       from  (select y.*,
                     sum(y.in_complete_boxes) over (partition by y.box_group) as sum_i_boxes,
                     163 - sum(case when y.dis_qty_after <= 163 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                     min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                     max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                     case 
                       when y.dis_qty_after <= 163 then 2
                       else case
                              when min(y.dis_qty_before) over (partition by y.box_group) < 163
                              and  max(y.dis_qty_after) over (partition by y.box_group) >= 163
                              then 1 
                              else 0 
                            end
                     end as state
              from  (select z.*,
                            sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                            sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after,
                            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,
                            case when sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) > 163 then 1 else 0 end as in_complete_boxes
                     from   z
             ) y
      ) x