November 9, 2009 at 9:56 am
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...
November 9, 2009 at 10:32 am
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
November 9, 2009 at 10:44 am
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
November 9, 2009 at 10:57 am
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
November 9, 2009 at 11:02 am
I got this error on your code :
row_number' is not a recognized function name.
I'm running on sql server 2005
November 9, 2009 at 11:06 am
while you are running on 2005, your database seems to be set for compatibility 80.
row_number function is compatibility 90 and above
Lowell
November 9, 2009 at 11:09 am
My bad this one is sql server 2000...
It seems I'm in deep...
November 9, 2009 at 12:13 pm
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply