SQL Query Help

Try this:

set nocount on
if object_id('tempdb..#units') is not null
	drop table #units 
create table #units(code varchar(5), units int)
insert #units values('A1',5),('A2',7),('A3',9),('A4',15),('A5',55)


declare
	@number int=114,
	@intermediate_number int,
	@max_value int=0,
	@intermediate_sum int=0,
	@factor int=0
set @intermediate_number=@number
select top 1
	@max_value=units
from 
	#units
where
	units<=@number 
order by 
	units desc

while @intermediate_sum<@number
begin
	set @intermediate_number=@intermediate_number-@intermediate_sum
	set @factor=@intermediate_number/@max_value
	set @intermediate_sum=@intermediate_sum+@max_value*isnull(nullif(@factor,0),1)
	print 'val: '+cast(@max_value as varchar)

	select top 1
		@max_value=units
	from 
		#units
	where
		units<=@intermediate_sum 
	order by 
		units desc
end

select top 1
	@max_value=units
from 
	#units
where
	units>=@intermediate_number 
order by 
	units
print 'val: '+cast(@max_value as varchar)

Select allOpen in new window

A recursive function should help:

CREATE FUNCTION GetBoxes( @NumBox INT)
RETURNS @Boxes TABLE (code CHAR(2), Data INT)
AS
BEGIN

	DECLARE @MaxData INT

	SELECT @MaxData=MAX(box.data) FROM box WHERE box.data <= @NumBox

	IF @MaxData IS NOT NULL
		BEGIN
			INSERT INTO @Boxes
			SELECT box.code, box.data
			FROM box
			WHERE box.data = @MaxData

			SELECT @NumBox=@NumBox-data
			FROM @Boxes
		
			IF @NumBox > 0 
				INSERT INTO @Boxes SELECT * FROM GetBoxes(@NumBox)
		END

	RETURN
END

SELECT * FROM GetBoxes(115)

Select allOpen in new window

Berteoc,

if I do :  number = 6

result is :
val: 5
val: 5
val: 5

its not good... it should be    A1  and A1

if I do 4   i have a infinite loop

4 should be   :  A1 only


45  I get:    15 and 55   seems wrong because it should be:    either A5 only or    A4 and A4 and A4  but   I prefer on multiple choices  the smallest so  A5 only

How does this work?

set nocount on
if object_id('tempdb..#units') is not null
	drop table #units 
create table #units(code varchar(5), units int)
insert #units values('A1',5),('A2',7),('A3',9),('A4',15),('A5',55)
--select * from #units

declare
	@number int=10,
	@intermediate_number int,
	@max_value int=0,
	@intermediate_sum int=0,
	@factor int=0
set @intermediate_number=@number
set @max_value=isnull((select top 1
	units
from 
	#units
where
	units<=@number 
order by 
	units desc), (select top 1 units from #units order by units))

while @intermediate_sum<@number
begin
	set @intermediate_number=@intermediate_number-@intermediate_sum
	set @factor=case when @max_value<>0 then @intermediate_number/@max_value else 1 end
	set @intermediate_sum=@intermediate_sum+@max_value*isnull(nullif(@factor,0),1)
	print 'val: '+cast(@max_value as varchar)+' '+cast(@intermediate_sum as varchar)

	select top 1
		@max_value=units
	from 
		#units
	where
		units<=@intermediate_sum 
	order by 
		units desc
	if @number<5
		break
end

if @number>(select top 1 units from #units order by units) and @intermediate_sum<@number
begin
	select top 1
		@max_value=units
	from 
		#units
	where
		units>=@intermediate_number 
	order by 
		units
	print 'val: '+cast(@max_value as varchar)
end

Select allOpen in new window

if I put :  56
your result is :

val: 55 55
val: 55 110

i dont understand ?   Shouldnt be     55  and 5   ?   (  A5  and A1 )

if 45 :
your result :

val: 15 45         what does it means?  looks like you are teling me I need only 1 box (1 box of A4)   but it should be A5

Replace this line:

      print 'val: '+cast(@max_value as varchar)+' '+cast(@intermediate_sum as varchar)

with this

      print 'val: '+cast(@max_value as varchar)

ok I did.. but still.. looks wrong (my comment above)
thanks for help..

PhilippeRenaud, you tried my function?

yes but im having a Nested limited level 32 reached ?! ..

Can you give me with which value are you having the error?

i do this : SELECT * FROM GetBoxes(55)
and im having this :

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.






ALTER FUNCTION [dbo].[GetBoxes]( @NumBox INT)
RETURNS @Boxes TABLE (code CHAR(2), Data INT)
AS
BEGIN

      DECLARE @MaxData INT

      SELECT @MaxData=MAX(box.Quantity) FROM Cashier_BoxInfo box WHERE box.Quantity <= @NumBox

      IF @MaxData IS NOT NULL
            BEGIN
                  INSERT INTO @Boxes
                  SELECT box.boxeType, box.Quantity
                  FROM Cashier_BoxInfo box
                  WHERE box.Quantity = @MaxData      
                  and box.Season = 'Spring'

                  SELECT @NumBox=@NumBox-data
                  FROM @Boxes
            
                  IF @NumBox > 0
                        INSERT INTO @Boxes SELECT * FROM GetBoxes(@NumBox)
            END

      RETURN
END

I don't know the data type that you have for boxeType so I used CHAR(2) like I saw in the examples you posted. You need to change to the correct data type so you won't have the truncate warning.

Question:

How do you solve 10?
9 and 5 or
7 or 5

you are right sorry.

ok so if I try :   SELECT * FROM GetBoxes(100)
im having :  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


if I try:  SELECT * FROM GetBoxes(6)
im having Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

for 6 it would need to be:   A1  and A1  obviously.


if Itry:  SELECT * FROM GetBoxes(56)
I have:  A5     55     but its not good because im missing 1 item.  it would be   A5    55    and A1    5

well,  for 5:     it fits inside a complete  A1


for 10:     it would be a A4   since A3 is  9  and A4 is 15 ..    the best solution is always with less boxes...
so 10  I would need result:   A4

for 9:     only A3

Ok, now I think it's fixed. Make yours changes and run it again please:

CREATE FUNCTION GetBoxes( @NumBox INT)
RETURNS @Boxes TABLE (code CHAR(2), Data INT)
AS
BEGIN

	DECLARE @MaxData INT

	SELECT @MaxData=MAX(box.data) FROM box WHERE box.data <= @NumBox

	IF @MaxData IS NULL
			INSERT INTO @Boxes
			SELECT box.code, box.data
			FROM box
			WHERE box.data = (SELECT MIN(box.data) FROM box WHERE box.data > @NumBox)
	ELSE
		BEGIN
			INSERT INTO @Boxes
			SELECT box.code, box.data
			FROM box
			WHERE box.data = @MaxData

			SELECT @NumBox=@NumBox-data
			FROM @Boxes
		
			IF @NumBox > 0 
				INSERT INTO @Boxes SELECT * FROM GetBoxes(@NumBox)
		END

	RETURN
END

SELECT * FROM GetBoxes(167)

Select allOpen in new window

if i do :  SELECT * FROM GetBoxes(100)

i receive :
A5      55
A4      15
A4      15
A4      15

do you have this also ?

it should be:  A5   and A5     (smallest number of boxes)
sorry i might not have told this in the first comment, I did in aboves comments... i hope its not a huge thing to fix for you.

But if you have A5 + A5 will be 110
A5 + A4 + A4 + A4 it's exactly 100

I understnad, but for the real situation, its better to only have 2 Box, even if the last of the two stil has space in it..

Just for I understand the logic. If it's 61 what should be returned?:
A5 + A5 = 110
A5 + A4 = 70
A5 + A3 = 64
A5 + A2 = 62

I think is necessary to put the rule in words so that it will apply for any number. So far both of use who tried had in mind different solutions than the actual desired one.

In my view 10 should be solved with either 7,5 or 9,5 but now you say 15 is the desired one.

Please put the general rule in words.

ok Im putting the rules, sorry for that you guys are right. :

10:     A4.    (because    7,5   is 2 box   9,5 is two box)  so  A4  is better because its one box.  A5 would NOT be good even if its 1 box because its way too big and 10 goes in A4 and since A4 is smaller and it fits, A4 wins over A5  ...


61:    should be:  A5  + A2   (same logic, a5 + a3 yes it works, but A2 works also and A2 is smaller so its preferable)


80 :   A5  and A5  

111:    A5,  A5 and A1

3:  A1

6:  A2

16:   A5

Ok. This changes all the logic that I putted in the function.
Will work on that.

sorry for that.

try this.

declare @table table (code varchar(2),Data int)
insert @table values ('A1',5),('A2',7),('A3',9),('A4',15),('A5',55)
declare @val int = 111

declare @rem int = @val
declare @result table (ID int identity,code varchar(2),no_box int,rem_val int)

while @rem <> 0
begin
;WITH cte as (
SELECT * ,row_number() over (order by case when no_box = 1 and rem_val = 0 then 1 else 2 end,no_box,rem_val,Data) rn
  FROM (
SELECT *,(@rem/Data) no_box,(@rem%Data) rem_val FROM @table) t1)
insert @result
select code,no_box,rem_val from cte where rn = 1
select @rem = case when no_box = 0 then 0 else rem_val end from @result where ID = (select max(ID) from @result)
end

select code,sum(case when no_box = 0 then 1 else no_box end) no_box from @result group by code
/*result
code	no_box
A1	1
A5	2
*/

Select allOpen in new window

Hi scott, why if I do 99  you give me :


99      3610      25      3      3610      25      1

why not:   99      3610      25      4           ?

i mena, both are fine, I was just wondering why thanks..

You must have adjusted the input data, since your original example didn't have a 25.

That's just the way my code works: it always computes two results (until everything fits exactly into one), even if they are the same.  It's easy enough to join the first result to a sequential numbers table and explode each to a separate line, as you were showing earlier:

25
25
25
25

Thanks to everybody.

It appears that you have not tried other solutions. That's ok as long as you got a working solution.