Lowest amount of the combination

Question

Lowest amount of the combination

Mkumari

Hall of Fame

Points: 3498
More actions
October 1, 2003 at 4:28 pm

#159902

Hi,
I have a table with following data in it
Auto_Type,1Auto,2-4Auto,5-9Auto,10-24Auto
Car, $175, $225, $300, $350
SUV, $275, $375, $475, $650
MiniVan, $375, $575, $875, $1275
The scenario is the Minivan is at the highest level and the car at lowest level. The lower level can goto the upper level but upper level cannot come to the lower level. When the user selects different combinations of auto's we should give the best value in the combination of all the categories.
For example I have combination of 9 Cars, 5 SUV's and 3 Minivans I want the lowest value when I do all the combinations like:
1. 17 Minivans = $1275
2. 16 Minivans & 1 SUV = 1550
3. 15 Minivans & 2 SUV = 1650
.....
.. 13 Minivans & 3 SVU & 1 Car = 1825
....
.. 4 Minivans & 4 SVU & 9 Car = 1250
after doing all the different combinations we will get the least amount in one of the combinations we want to take that price.In the above example imagine that we have a number 1175 which is least value from one of the combination I need that price since it is the least price.
I want to write a stored procedure which does this process when three values are passed into the SP parameters.
Thanks,
Mkumari

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 1

I do not really understand your question.

If I see well the least value would not be 1175 as you said but 350 if you take 10-24 cars or 1000 if you have to have at least 1 of each category (15 cars = 350, 1 suv = 275, 1 minivan = 375).

Could you specify what should be the input and the output of the proc?

Bye

Gabor

Bye
Gabor

Vladan SSC-Insane Points: 22072 More actions · Answer 2

It is rather hard to understand, but I think a begin to see a light.. though I'm still only guessing.

"The lower level can goto the upper level but upper level cannot come to the lower level." - that probably means, that there have to be at least 3 minivans, since the original set is 9+5+3, and minivan is "highest level".

To put it in another words, you are not allowed to decrease the amount of most expensive article below original value... Well, but that still does not get us to the correct figures... maybe it is also, that the sum of the two "higher" levels (SUV+Van) may not be less than the original sum of the same - in this example 8?

Hard to tell, Mkumari... please try to expand the explanation a bit

Edited by - Vladan on 10/02/2003 05:25:03 AM

Mkumari Hall of Fame Points: 3498 More actions · Answer 3

Thanks for the response.

I am forwarding you a link, right now it is in a web app.I think it will give you a clear understanding. I want to do the same thing in a stored procedure.

http://www.nbaa.org/membership/calcduesprint.html

I have used it as cars instead of aircrafts in my example.

I want the same results in the stored procedure but the just the final least value.

Mkumari

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

Don't mean to be harsh, but you're evidently already working beyond your RDBMS knowledge by creating that schema. If you want to do this calculation on the server side, you've got to either gain more knowledge or hire the expertise to create and maintain the code. If I were to post a better schema and a T-SQL solution for your calculation, then you would probably be unable to understand (and maintain) the code. If your client side calculation works, that's obviously where your expertise lies, so leave it there until you learn enough about databases and SQL to tackle this yourself. If you repost in a month with a good schema and some viable attempts at the SQL, I'll be glad to help you then (if I'm still around ).

The solution is greatly simplified by the apparent need for the SP to return just one value, i.e., you don't need to return the optimal combination of types, just the bottom line.

--Jonathan

Mkumari Hall of Fame Points: 3498 More actions · Answer 5

I appriciate your response and thanks for your suggestions but I need to get this done in one days time that's why I have posted it here to get quick ideas to create it.