March 19, 2014 at 11:52 pm
create table #sample (
product varchar(100),
Price varchar(100)
)
insert into #sample values ('Pen',10)
insert into #sample values ('DVD',29)
insert into #sample values ('Pendrive',45)
insert into #sample values ('Mouse',12.5)
insert into #sample values ('TV',49)
select * from #sample
consider this situation ...
I'm having 1000$, I wanted to buy something listed above.
I want to spend all the amount
So I need a query which gives how much units in all products will cost 1000$
any help ?
March 20, 2014 at 12:04 am
This is similar to the "greedy knapsack" problem (Googleable) but I can tell you flat out it ain't gonna be easy to do in SQL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 20, 2014 at 4:16 pm
SELECT * ,
MaxUnits = CONVERT(INT, 1000 / CONVERT(DECIMAL(5), Price))
FROM #sample
Wes
(A solid design is always preferable to a creative workaround)
March 21, 2014 at 4:47 am
whenriksen (3/20/2014)
SELECT * ,MaxUnits = CONVERT(INT, 1000 / CONVERT(DECIMAL(5), Price))
FROM #sample
We should combine all the products in the purchase list ... not a single product.
April 18, 2014 at 3:19 am
Any help ??
April 18, 2014 at 3:35 am
vignesh.ms (4/18/2014)
Any help ??
Out of curiosity i want to ask you that where did you get this problem 🙂
However as dwain mentioned it is not going to be easy one. Infact it is difficult in Mathematics as well to find a generic solution for it.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
April 18, 2014 at 4:45 am
what do you want the result to be
100 pens or 80 mice ....you could have either for exactly $1000
or a mixture of other products as well......????
as others have already pointed out to you...this can be a very complicated process.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2014 at 6:00 am
This sounds like a contest or homework problem to me.
April 18, 2014 at 6:44 am
Brandie Tarvin (4/18/2014)
This sounds like a contest or homework problem to me.
Should be mixture of all products..
I'm eagerly waiting for the query....
Pls try it out..
April 18, 2014 at 6:55 am
vignesh.ms (4/18/2014)
Brandie Tarvin (4/18/2014)
This sounds like a contest or homework problem to me.Should be mixture of all products..
I'm eagerly waiting for the query....
Pls try it out..
Sorry. I don't do other people's homework. Nor do I give them contest answers. If there's a contest, post a link to it and we'll enter individually with our own code. If this is homework, avail yourself of Books Online. It has lots of code samples.
April 18, 2014 at 7:32 am
vignesh.ms (4/18/2014)
Brandie Tarvin (4/18/2014)
This sounds like a contest or homework problem to me.Should be mixture of all products..
I'm eagerly waiting for the query....
Pls try it out..
This is an EXTREMELY complex algorithm. It is similar to the bin packing problem. Both of these are incredibly complex mathematical calculations. Did you even use google as Dwain suggested? If so maybe you stumbled on the wikipedia article. If not here is the link. http://en.wikipedia.org/wiki/Knapsack_problem
In that article you will find enough information to code a "solution". I sure hope you don't need the results quickly because nested loops 2 and 3 deep perform horribly in sql server. In short, t-sql is completely the wrong tool for this.
Keep in mind we are all volunteers around here. So when you say things like "I'm eagerly waiting for the query..." you should expect that most people here will walk away. We don't owe you anything and we do not get anything from posting other than the satisfaction of helping others. When those people expect our help or demand it the satisfaction is soured. We like to help people who want to learn, not be their personal gratis consultant.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2014 at 8:24 am
vignesh.ms (4/18/2014)
Brandie Tarvin (4/18/2014)
This sounds like a contest or homework problem to me.Should be mixture of all products..
I'm eagerly waiting for the query....
Pls try it out..
ok...here is an answer
;
WITH acte
AS (
SELECT MAX(Price) AS mp
FROM sample
WHERE (1000.00 % Price = 0)
)
SELECT sample.product
FROM acte
INNER JOIN sample ON acte.mp = sample.Price
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2014 at 8:25 am
This is an EXTREMELY complex algorithm. It is similar to the bin packing problem. Both of these are incredibly complex mathematical calculations. Did you even use google as Dwain suggested? If so maybe you stumbled on the wikipedia article. If not here is the link. http://en.wikipedia.org/wiki/Knapsack_problem
In that article you will find enough information to code a "solution". I sure hope you don't need the results quickly because nested loops 2 and 3 deep perform horribly in sql server. In short, t-sql is completely the wrong tool for this.
Keep in mind we are all volunteers around here. So when you say things like "I'm eagerly waiting for the query..." you should expect that most people here will walk away. We don't owe you anything and we do not get anything from posting other than the satisfaction of helping others. When those people expect our help or demand it the satisfaction is soured. We like to help people who want to learn, not be their personal gratis consultant.
I really got lot of help from you guys.I don't demand, how could I ?because of enthusiasm I have posted like that .. If it make you think like that I apologize for that & thanks for your suggestion...
April 18, 2014 at 8:32 am
J Livingston SQL (4/18/2014)
vignesh.ms (4/18/2014)
Brandie Tarvin (4/18/2014)
This sounds like a contest or homework problem to me.Should be mixture of all products..
I'm eagerly waiting for the query....
Pls try it out..
ok...here is an answer
;
WITH acte
AS (
SELECT MAX(Price) AS mp
FROM sample
WHERE (1000.00 % Price = 0)
)
SELECT sample.product
FROM acte
INNER JOIN sample ON acte.mp = sample.Price
No. Not this answer.
I need combination of all products & how many of them will cumulatively cost $1000
April 18, 2014 at 8:39 am
vignesh.ms (4/18/2014)
This is an EXTREMELY complex algorithm. It is similar to the bin packing problem. Both of these are incredibly complex mathematical calculations. Did you even use google as Dwain suggested? If so maybe you stumbled on the wikipedia article. If not here is the link. http://en.wikipedia.org/wiki/Knapsack_problem
In that article you will find enough information to code a "solution". I sure hope you don't need the results quickly because nested loops 2 and 3 deep perform horribly in sql server. In short, t-sql is completely the wrong tool for this.
Keep in mind we are all volunteers around here. So when you say things like "I'm eagerly waiting for the query..." you should expect that most people here will walk away. We don't owe you anything and we do not get anything from posting other than the satisfaction of helping others. When those people expect our help or demand it the satisfaction is soured. We like to help people who want to learn, not be their personal gratis consultant.
I really got lot of help from you guys.I don't demand, how could I ?
because of enthusiasm I have posted like that .. I apologize for that ...
And thanks for your suggestion...
No problem. Quite possible a language barrier issue. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply