April 18, 2014 at 8:41 am
try this article........
________________________________________________________________
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:44 am
J Livingston SQL (4/18/2014)
try this article........
AWESOME! Thanks for that link. Gail, as always, does wonderful work.
April 18, 2014 at 8:51 am
Brandie Tarvin (4/18/2014)
J Livingston SQL (4/18/2014)
try this article........AWESOME! Thanks for that link. Gail, as always, does wonderful work.
seems my googlefoo is working today 😀
this a problem that I was aware of, but haven't had to resolve before ...having just started reading Gail's article...I am now set for the long weekend to try and understand it......just love SQL :w00t:
________________________________________________________________
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 9:20 am
1861 possible solutions...
PenDVDPenDriveMouseTV
115414
...
67 4261
No cursors, no loops..
But I took 6 minutes to run the query..
April 18, 2014 at 9:30 am
Louis Hillebrand (4/18/2014)
1861 possible solutions...PenDVDPenDriveMouseTV
115414
...
67 4261
No cursors, no loops..
But I took 6 minutes to run the query..
care to share the code Louis?
________________________________________________________________
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 9:51 am
there are a few links here to help you, but let me ask you this. How would you do this without SQL? What is the algorithm you'd use?
Once you have that, then you can start to write a query. Chances are a single query will be complex, but if you think about what you need to do, then you can probably get close and we can help you find an answer.
However we don't like to just do the work for you. You should make some attempt at a query. The result shown by Louis is a good hint.
April 18, 2014 at 10:15 am
This looks me as a homework job, so the OP has to find the solution, that's why I didn't give the query,
Just a hint: Min quantity = 1, max = 1000 / Price.. + a tally table.
April 18, 2014 at 10:31 am
Louis Hillebrand (4/18/2014)
This looks me as a homework job, so the OP has to find the solution, that's why I didn't give the query,Just a hint: Min quantity = 1, max = 1000 / Price.. + a tally table.
fair enough 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 19, 2014 at 3:23 am
Louis Hillebrand (4/18/2014)
This looks me as a homework job, so the OP has to find the solution, that's why I didn't give the query,Just a hint: Min quantity = 1, max = 1000 / Price.. + a tally table.
Any other clue ???
April 19, 2014 at 4:55 am
Your question is "Need query for problem".
Would you mind sharing the logic you came up with to solve the problem? We might be able to help you "translate" it into T-SQL, if possible.
As others already stated, the problem itself is rather complex.
What exactly are you looking for?
A few hints how to tackle the problem -> already available
Links to a few SQL code samples how to solve it -> already provided
A coded solution that specifically solve your issue -> not available. Either use the hints you have so far or hire someone to solve it for you.
You might want to rethink the way you post.
Something along "Nope, not what I want. Next one!" doesn't help at all...
April 19, 2014 at 7:46 am
LutzM (4/19/2014)
Your question is "Need query for problem".Would you mind sharing the logic you came up with to solve the problem? We might be able to help you "translate" it into T-SQL, if possible.
As others already stated, the problem itself is rather complex.
What exactly are you looking for?
A few hints how to tackle the problem -> already available
Links to a few SQL code samples how to solve it -> already provided
A coded solution that specifically solve your issue -> not available. Either use the hints you have so far or hire someone to solve it for you.
You might want to rethink the way you post.
Something along "Nope, not what I want. Next one!" doesn't help at all...
Im newbie to SQL, that's why I expect you guys... Looking into samples and looking into code, by the way I could equip myself.This is not meaning that I'm expecting someone to do my job..
If my post's make you think like that, please understand that is a language barrier issue with me...
April 19, 2014 at 7:51 am
Well, here is the other part of the problem, and without actually scrolling back through, I seem to remember someone asking you to post what you have tried to solve your problem. Don't recall you posting anything yet in that regard.
Why not give it a shot yourself and show us what you have done.
April 19, 2014 at 8:30 am
this is not the fine answer & also not mine...
It took much time to solve 1000 , so given answer is for 100
WITH Base(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
)
, Unit(N) AS (
SELECT Row_Number() Over (ORDER BY (SELECT NULL)) - 1
FROM Base
)
, Counter(N) AS (
SELECT u.n + 10*te.n
FROM Unit u
CROSS JOIN Unit te --tens
WHERE u.n + 10*te.n <= (SELECT 100 / Min(Price) FROM Sample)
)
, Pens AS (
SELECT product
, Price = price * N
, Quantity = N
FROM sample
CROSS JOIN Counter
WHERE product = 'Pen'
AND N <= 100 / Price
)
, DVDs AS (
SELECT product
, Price = price * N
, Quantity = N
FROM sample
CROSS JOIN Counter
WHERE product = 'DVD'
AND N <= 100 / Price
)
, Pendrives AS (
SELECT product
, Price = price * N
, Quantity = N
FROM sample
CROSS JOIN Counter
WHERE product = 'Pendrive'
AND N <= 100 / Price
)
, Mouses AS (
SELECT product
, Price = price * N
, Quantity = N
FROM sample
CROSS JOIN Counter
WHERE product = 'Mouse'
AND N <= 100 / Price
)
, TVs AS (
SELECT product
, Price = price * N
, Quantity = N
FROM sample
CROSS JOIN Counter
WHERE product = 'TV'
AND N <= 100 / Price
)
SELECT TOP 10
Pen = p.Quantity
, DVD = d.Quantity
, Pendrive = pe.Quantity
, Mouse = m.Quantity
, TV = t.Quantity
, Price = p.Price + d.price + pe.price + m.price + t.price
FROM pens p
CROSS JOIN DVDs d
CROSS JOIN Pendrives pe
CROSS JOIN Mouses m
CROSS JOIN TVs t
WHERE p.Price + d.price + pe.price + m.price + t.price <= 100
ORDER BY p.Price + d.price + pe.price + m.price + t.price DESC
April 19, 2014 at 2:10 pm
In addition to previous solution, I give mine:
I only added a Tally CTE to my original query, http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
It's slow but gives the results, something to examine and optimize..
CREATE TABLE #Sample (
product varchar(100),
Price decimal(9,2)
)
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);
DECLARE @TotalPrice decimal(9,2) = 200.00;
WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y),
BX (Product, Price, Max_Qty) AS (
SELECT Product,
Price,
Floor(@TotalPrice / Price )
FROM #Sample
),
Pen (Price, Qty) AS (
SELECTPrice * N, N FROMBX INNER JOIN Tally T ON T.N <= BX.Max_Qty WHERE Product = 'Pen'
),
DVD (Price, Qty) AS (
SELECTPrice * N, N FROMBX INNER JOIN Tally T ON T.N <= BX.Max_Qty WHERE Product = 'DVD'
),
PenDrive (Price, Qty) AS (
SELECTPrice * N, N FROMBX INNER JOIN Tally T ON T.N <= BX.Max_Qty WHERE Product = 'PenDrive'
),
Mouse (Price, Qty) AS (
SELECTPrice * N, N FROMBX INNER JOIN Tally T ON T.N <= BX.Max_Qty WHERE Product = 'Mouse'
),
TV (Price, Qty) AS (
SELECTPrice * N, N FROMBX INNER JOIN Tally T ON T.N <= BX.Max_Qty WHERE Product = 'TV'
)
SELECTPen.Qty[Pen],
DVD.Qty[DVD],
PenDrive.Qty[PenDrive],
Mouse.Qty[Mouse],
TV.Qty[TV]
FROMPen CROSS JOIN
DVD CROSS JOIN
PenDrive CROSS JOIN
Mouse CROSS JOIN
TV
WHEREPen.Price + DVD.Price + PenDrive.Price + Mouse.Price + TV.Price = @TotalPrice
DROP TABLE #Sample
April 19, 2014 at 2:11 pm
Of course @Totalprice should be 1000.00, not 200.00, that was just for checking the query...
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply