SQL Query Help

Hello EE,

I need help with a query.

Lets say I have a table with this data :

Code      Data
A1        5
A2        7
A3        9
A4       15
A5       55

So those are boxes.. box A1 has a place of 5 units.  A2 7 units and so on.

So, what I want is... a query that if I send a parameter of lets say the number 115
I would like that for 115 units... it returns me the results of  2 box  A5  (  55 + 55   = 110)   and another row with box A1

do you see where im getting...
if it was 116

it would be 2 box A5 and 1 box A2 ...
I need this in sQL ....

thanks EE ....
LVL 1
PhilippeRenaudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
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)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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)

Open in new window

0
PhilippeRenaudAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ZberteocCommented:
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

Open in new window

0
PhilippeRenaudAuthor Commented:
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
0
ZberteocCommented:
Replace this line:

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

with this

      print 'val: '+cast(@max_value as varchar)
0
PhilippeRenaudAuthor Commented:
ok I did.. but still.. looks wrong (my comment above)
thanks for help..
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
PhilippeRenaud, you tried my function?
0
PhilippeRenaudAuthor Commented:
yes but im having a Nested limited level 32 reached ?! ..
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you give me with which value are you having the error?
0
PhilippeRenaudAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
ZberteocCommented:
Question:

How do you solve 10?
9 and 5 or
7 or 5
0
PhilippeRenaudAuthor Commented:
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
0
PhilippeRenaudAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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)

Open in new window

0
PhilippeRenaudAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
But if you have A5 + A5 will be 110
A5 + A4 + A4 + A4 it's exactly 100
0
PhilippeRenaudAuthor Commented:
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..
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
ZberteocCommented:
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.
0
PhilippeRenaudAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. This changes all the logic that I putted in the function.
Will work on that.
0
PhilippeRenaudAuthor Commented:
sorry for that.
0
Scott PletcherSenior DBACommented:
I think the code below will do it, since the fewest number of boxes means you never go past 2 types of boxes (although it could just be one), and the second box qty will always be 0 or 1:

DECLARE @maximum_units int
SELECT @maximum_units = MAX(units)
FROM #units

SELECT
    total_units,
    units_lookup_1.*,
    box1_qty,
    units_lookup_2.*,
    CASE WHEN remaining_units_1 > 0 THEN 1 ELSE 0 END AS box2_qty

--this is just the sample quantities you gave in one of your posts as test data;
--naturally replace this with your actual table name for "live" processing.
FROM (
    SELECT 10 AS total_units UNION ALL
    SELECT 61 UNION ALL
    SELECT 80 UNION ALL
    SELECT 111 UNION ALL
    SELECT 3 UNION ALL
    SELECT 6 UNION ALL
    SELECT 16    
) AS test_data

CROSS APPLY (
    SELECT TOP (1)
        code, units
    FROM #units
    WHERE
        (total_units > @maximum_units AND units <= total_units) OR
        (total_units <= @maximum_units AND units >= total_units)
    ORDER BY
        CASE WHEN total_units > @maximum_units THEN units ELSE 0 END DESC,
        units ASC
) AS units_lookup_1
CROSS APPLY (  
    SELECT
        CASE WHEN units_lookup_1.units > total_units THEN 1 ELSE total_units / units_lookup_1.units END AS box1_qty,        
        CASE WHEN units_lookup_1.units > total_units THEN 0 ELSE
        total_units - (total_units / units_lookup_1.units * units_lookup_1.units) END AS remaining_units_1
) AS assign_alias_1
OUTER APPLY (
    SELECT TOP (1)
        code, units
    FROM #units
    WHERE
        remaining_units_1 > 0 AND
        units >= remaining_units_1
    ORDER BY
        units ASC
) AS units_lookup_2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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
*/

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry Philippe but didn't have time yesterday to work in the new function.
Try this one now:
CREATE FUNCTION [dbo].[GetBoxes2]( @NumBox INT)
RETURNS @Boxes TABLE (code CHAR(2), Data INT)
AS
BEGIN

	DECLARE @MaxData INT

	SELECT @MaxData=MAX(box.data) FROM box 

	IF @NumBox > @MaxData 
		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 GetBoxes2(@NumBox)
		END
	ELSE
			INSERT INTO @Boxes
			SELECT box.code, box.data
			FROM box
			WHERE box.data = (SELECT MIN(data) FROM box WHERE (data - @NumBox) >= 0)
	RETURN
END

Open in new window

0
PhilippeRenaudAuthor Commented:
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..
0
Scott PletcherSenior DBACommented:
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
0
PhilippeRenaudAuthor Commented:
Thanks to everybody.
0
SharathData EngineerCommented:
It appears that you have not tried other solutions. That's ok as long as you got a working solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.