Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Query Help

Posted on 2014-09-09
Medium Priority
305 Views
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 ....
0
Question by:PhilippeRenaud
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 13
• 9
• 5
• +2

LVL 27

Expert Comment

ID: 40313322
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)
``````
0

LVL 52

Expert Comment

ID: 40314073
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)
``````
0

LVL 1

Author Comment

ID: 40314430
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

LVL 27

Expert Comment

ID: 40314529
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
``````
0

LVL 1

Author Comment

ID: 40314548
if I put :  56

val: 55 55
val: 55 110

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

if 45 :

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

LVL 27

Expert Comment

ID: 40314555
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

LVL 1

Author Comment

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

LVL 52

Expert Comment

ID: 40314581
PhilippeRenaud, you tried my function?
0

LVL 1

Author Comment

ID: 40314584
yes but im having a Nested limited level 32 reached ?! ..
0

LVL 52

Expert Comment

ID: 40314606
Can you give me with which value are you having the error?
0

LVL 1

Author Comment

ID: 40314647
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

LVL 52

Expert Comment

ID: 40314673
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

LVL 27

Expert Comment

ID: 40314696
Question:

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

LVL 1

Author Comment

ID: 40314698
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

LVL 1

Author Comment

ID: 40314705
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

LVL 52

Expert Comment

ID: 40314717
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)
``````
0

LVL 1

Author Comment

ID: 40314743
if i do :  SELECT * FROM GetBoxes(100)

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

LVL 52

Expert Comment

ID: 40314797
But if you have A5 + A5 will be 110
A5 + A4 + A4 + A4 it's exactly 100
0

LVL 1

Author Comment

ID: 40314801
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

LVL 52

Expert Comment

ID: 40314819
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

LVL 27

Expert Comment

ID: 40314825
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

LVL 1

Author Comment

ID: 40314862
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

LVL 52

Expert Comment

ID: 40314918
Ok. This changes all the logic that I putted in the function.
Will work on that.
0

LVL 1

Author Comment

ID: 40314922
sorry for that.
0

LVL 70

Accepted Solution

Scott Pletcher earned 1200 total points
ID: 40315857
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

LVL 41

Expert Comment

ID: 40315948
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
*/
``````
0

LVL 52

Assisted Solution

Vitor Montalvão earned 800 total points
ID: 40316414
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
``````
0

LVL 1

Author Comment

ID: 40317006
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

LVL 70

Expert Comment

ID: 40318222
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

LVL 1

Author Closing Comment

ID: 40318409
Thanks to everybody.
0

LVL 41

Expert Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …