Solved

SQL Query Help

Posted on 2014-09-09
31
273 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 26

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 45

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
 
LVL 26

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 26

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 45

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 45

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 45

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 26

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

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 45

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 45

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 26

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 45

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 69

Accepted Solution

by:
ScottPletcher earned 300 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 40

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 45

Assisted Solution

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

Expert Comment

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now