|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 11:57 AM
Points: 5,
Visits: 20
|
|
Hi everyone,
I need you help here. I was told to make a stored Procedure that return a simple field base on a search of 3 fields. Width, Height and Depth. I need to return the product code where the dimensions are all equals or the next highest.
I give you an example :
Rows :
ProdCode, Width,Height, Depth
A | 2 |4 |6
B | 3 |4 |6
C |2 |5 |6
D |3 |4 |5
E |2 |4 |7
I want to find the product that have the nearest or next hight dimension for the following sizes :
Width : 1.5 Height : 4.2 and depth 6
It should return me only product C.
A variation can occurs on all 3 dimension...
and I'm kind of lost here...
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 3,920,
Visits: 10,577
|
|
here's how i would do it, not sure if it is what you want:
in the example you gave, there was only one possible solution for Width : 1.5 Height : 4.2 and depth 6
a better example would which is the best match for Width : 1.5 Height : 3and depth 5; in that case, there are 6 possible matching values...which is the best? i just assumed the importance of the order of wodth-height-depth, and took the first:
ProdCode Width Height Depth
--------- ------ ------- ------
A 2.00 4.00 6.00
E 2.00 4.00 7.00
C 2.00 5.00 6.00
D 3.00 4.00 5.00
B 3.00 4.00 6.00
create table #NoName(
ProdCode varchar(30),
Width decimal(6,2),
Height decimal(6,2),
Depth decimal(6,2) )
INSERT INTO #NoName
SELECT 'A', 2 ,4 ,6 UNION ALL
SELECT 'B', 3 ,4 ,6 UNION ALL
SELECT 'C',2 ,5 ,6 UNION ALL
SELECT 'D',3 ,4 ,5 UNION ALL
SELECT 'E',2 ,4 ,7
SELECT * FROM #NoName
WHERE Width >= 1.5
and Height>= 3
and depth >= 5
ORDER BY Width -1.5 ,Height -3,Depth -5
SELECT * FROM
(
SELECT
row_number() OVER(ORDER BY Width -1.5 ,Height -3,Depth -5) AS RW,
* FROM #NoName
WHERE Width >= 1.5
and Height>= 3
and depth >= 5
) MyAlias
WHERE RW =1
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 11:57 AM
Points: 5,
Visits: 20
|
|
In fact there should be only one row in the result. The order of priority is Width, Height, Depth
In the real world there is thousands of product code. And I'm lookin for a way to do the following
Find all Product code with a certain Width (or the next highest if not found)
Then filter to find all of those with a certain Heigth (or the next highest if not found)
And then from those find the one with the Depth (or the next highest if not found)
The result should give me only one row.
I must provide a solid and efficient way to do that. I was told it should be possible with only one Select stamentment and i want to avoid cursors or temp table if possible
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 3,920,
Visits: 10,577
|
|
you'll want to try my code featuring row_number at the bottom of the example i pasted.
let me know if that works for you.
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 11:57 AM
Points: 5,
Visits: 20
|
|
I got this error on your code :
row_number' is not a recognized function name.
I'm running on sql server 2005
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 3,920,
Visits: 10,577
|
|
while you are running on 2005, your database seems to be set for compatibility 80.
row_number function is compatibility 90 and above
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 11:57 AM
Points: 5,
Visits: 20
|
|
My bad this one is sql server 2000...
It seems I'm in deep...
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 3,920,
Visits: 10,577
|
|
in SQL server, there is always more than one way to skin a cat:
this should provide the same logic...the order by is the key:
create table #NoName(
ProdCode varchar(30),
Width decimal(6,2),
Height decimal(6,2),
Depth decimal(6,2) )
INSERT INTO #NoName
SELECT 'A', 2 ,4 ,6 UNION ALL
SELECT 'B', 3 ,4 ,6 UNION ALL
SELECT 'C',2 ,5 ,6 UNION ALL
SELECT 'D',3 ,4 ,5 UNION ALL
SELECT 'E',2 ,4 ,7
declare @Width decimal(6,2)
declare @Height decimal(6,2)
declare @Depth decimal(6,2)
SET @Width = 1.5
SET @Height = 3
SET @Depth = 5
SELECT TOP 1
* FROM #NoName
WHERE Width >= @Width
and Height>= @Height
and depth >= @Depth
ORDER BY Width - @Width ,Height - @Height,Depth -@Depth
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
