Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Help

Posted on 2014-09-09
31
Medium Priority
?
325 Views
Last Modified: 2014-09-11
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
Comment
Question by:PhilippeRenaud
  • 13
  • 9
  • 5
  • +2
31 Comments
 
LVL 27

Expert Comment

by:Zberteoc
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)

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
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)

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:Zberteoc
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

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40314548
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
 
LVL 27

Expert Comment

by:Zberteoc
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

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

Expert Comment

by:Vitor Montalvão
ID: 40314581
PhilippeRenaud, you tried my function?
0
 
LVL 1

Author Comment

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

Expert Comment

by:Vitor Montalvão
ID: 40314606
Can you give me with which value are you having the error?
0
 
LVL 1

Author Comment

by:PhilippeRenaud
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

by:Vitor Montalvão
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

by:Zberteoc
ID: 40314696
Question:

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

Author Comment

by:PhilippeRenaud
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

by:PhilippeRenaud
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

by:Vitor Montalvão
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)

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40314743
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40314797
But if you have A5 + A5 will be 110
A5 + A4 + A4 + A4 it's exactly 100
0
 
LVL 1

Author Comment

by:PhilippeRenaud
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

by:Vitor Montalvão
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

by:Zberteoc
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

by:PhilippeRenaud
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

by:Vitor Montalvão
ID: 40314918
Ok. This changes all the logic that I putted in the function.
Will work on that.
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40314922
sorry for that.
0
 
LVL 70

Accepted Solution

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

by:Sharath
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
*/

Open in new window

0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
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

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
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

by:Scott Pletcher
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

by:PhilippeRenaud
ID: 40318409
Thanks to everybody.
0
 
LVL 41

Expert Comment

by:Sharath
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question