How many bottles will fit in a box?

Question

How many bottles will fit in a box?

nimmi.smith-624103

SSC Eights!

Points: 876
More actions
May 20, 2009 at 2:33 pm

#221514

:w00t:My boss wants me to come up with some kind of logic to figure out how many bottles will fit a box. He has the box dimensions as
9 1/2" X 7 1/4" X 2 1/2"
9 X 7 X 510 X 8 X 6
12 X 10 X 8
14 X 14 X 10
He wants to know how many bottles of a product will fit a box. His bottle sizes are 75cc, 100 cc, 120 cc, 150 cc, 175 cc, 225 cc, 275 cc, 300 cc, 400 cc, 625 cc, 750 cc & 950 cc.
Has anyone done something like this before?
Any assistance would be most appreciated.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Not sure how this a a SQL Server question, but...

How about just getting some boxes and bottles of those sizes, and seeing how many of each type of bottle you can put in each type of box?

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 2

He wants me to do this in sql using some sort of an algorithm. If it was just one size bottles, I could pre determine of many of each kind of bottles would fit one box. He has mixed sized bottles and wants to know which box he should use-and it has to be done automatically by an sql stored proc or function. This guy tells me Oracle has such a function. I doubt that!!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

This cannot be done with the information you gave.

You need to know the physical dimensions of the bottles, not their volume.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

You need to know the dimensions of the bottles, not necessarily only the volume. Why don't you make one of each size box, and see which one holds the most bottles?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Given the proper information it should be possible to create a function/procedure to complete the necessary computations. Is there only one size box or do you have multiple sizes of boxes as well?

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 6

He has multiple box sizes and multiple bottle sizes. I will have him give me the the physical dimensions of the bottles, not their volume and repost the info again. Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

And another thought, you may need to use advanced math (calculus) if you are trying to maximize the number of bottles per box. With that, these calculations should probably be done in the application layer, not the database layer. When I worked at a Product Repair Facility, one of our programs did just this type of calculation for shipping parts to field engineers.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 8

What you have is called an np-complete or np-hard problem. It is also referenced as the bin packing problem.

Hugo has a great article on this exact issue at: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/30/bin-packing-part-1-setting-a-baseline.aspx

I suggest you start there and see where that takes you.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

nimmi.smith (5/20/2009)
He has multiple box sizes and multiple bottle sizes. I will have him give me the the physical dimensions of the bottles, not their volume and repost the info again. Thanks

I'd keep the volume, it is part of the description of the bottles.

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 10

Lynn: You hit the nail right on the head. That is exactly what he is looking for.This is a tiny shop. There is no front-end developer. Just myself as an SQL DbA & DBDev. And I am stumped on this one...You wouldn't happen to know what calculus function was used in the app you mentioned?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

nimmi.smith (5/20/2009)
Lynn: You hit the nail right on the head. That is exactly what he is looking for.This is a tiny shop. There is no front-end developer. Just myself as an SQL DbA & DBDev. And I am stumped on this one...You wouldn't happen to know what calculus function was used in the app you mentioned?

I wish. I was just a computer operator way back then so I have no idea how they made the calculations. It is something I'd probably have to work up from scratch.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

You would probably better off posting you question on a web site that is devoted to math, not SQL, to find an algorithm that can be used to solve the problem.

This sounds like a nontrivial problem, especially if you are mixing different sizes of bottles in the same box.

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 13

Thanks for the advise. I will find a math forum and post there. Any know of math forums?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Here is one I found with a Google search, http://www.mymathforum.com/.

If that one doesn't work, try Google to find some more.