Closest match (number ranges, not string)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Closest match (number ranges, not string)

Author

Topic

slabo
Starting Member

2 Posts

Posted - 2009-06-22 : 00:29:52

Hello;
I have some percentage ranges corresponding to chemical composition of alloys. Each set of percentages represent a certain grade.
Data structure as follows::

CREATE TABLE compositions
( grade SMALLINT NOT NULL PRIMARY KEY 
, a_min SMALLINT NOT NULL
, a_max SMALLINT NOT NULL
, b_min SMALLINT NOT NULL
, b_max SMALLINT NOT NULL
, c_min SMALLINT NOT NULL
, c_max SMALLINT NOT NULL
);

For a given alloy, I have a percentage value for each element. I want to select the grade that matches the given alloy. To do this i can do::


SELECT *
FROM compositions
WHERE $a_val BETWEEN a_min AND a_max
	and $b_val BETWEEN b_min AND b_max
	and $c_val BETWEEN c_min AND c_max
ORDER by grade ASC

And this would return the matching grades. Simple enough. But i would like to modify for the case that there are no exact matches. I would like the list the grades that are the closest match. For this i could use ::


SELECT grade
FROM compositions
WHERE NOT ( $a_val BETWEEN a_min AND a_max )
	or NOT ( $b_val  BETWEEN b_min AND b_max )
	or NOT ( $c_val BETWEEN c_min AND c_max )
ORDER     BY min(abs(a_min - $a_value),abs(a_max - $a_value)) 
+ min(abs(b_min - $b_value),abs(b_max - $b_value))  
+ min(abs(c_min - $c_value),abs(c_max - $c_value)) ASC

Obviously the min() function is wrong, but that's what i would write in C++. Also, min(abs(a_min - $a_value) must not be added to the score when a_val is between a_min and a_max, but again, i don't know how to do this in SQL.

Also, is there a way to do this in one query? If there are existing grades that match, show them, else, show the closest matches.

Sample data::


INSERT INTO compositions VALUES ( 1 , 10,20 , 2,6 , 8,12 )
INSERT INTO compositions VALUES ( 2, 21,25 , 7,11 , 76,80 )
INSERT INTO compositions VALUES ( 3 , 21,25 , 8,9 , 20,25 )

Sample output for given a_val=22,b_val=10,c_val=19

3 21 25 8 9 20 25

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 02:55:39

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 02:57:49

[code]DECLARE @Compositions TABLE
(
grade SMALLINT NOT NULL PRIMARY KEY,
a_min SMALLINT NOT NULL,
a_max SMALLINT NOT NULL,
b_min SMALLINT NOT NULL,
b_max SMALLINT NOT NULL,
c_min SMALLINT NOT NULL,
c_max SMALLINT NOT NULL
)

INSERT @Compositions
SELECT 1, 10, 20, 2, 6, 8, 12 UNION ALL
SELECT 2, 21, 25, 7, 11, 76, 80 UNION ALL
SELECT 3, 21, 25, 8, 9, 20, 25

DECLARE @a SMALLINT,
@b SMALLINT,
@c SMALLINT

SELECT @a = 22,
@b = 10,
@c = 19

SELECT TOP 1 *
FROM @Compositions
ORDER BY ABS(a_min - @a) + ABS(a_max - @a)
+ ABS(b_min - @b) + ABS(b_max - @b)
+ ABS(c_min - @c) + ABS(c_max - @c)[/code]

E 12°55'05.63"
N 56°04'39.26"

slabo
Starting Member

2 Posts

Posted - 2009-06-22 : 04:33:55

yes, i am using SQL server 2005
But what you wrote does not check if the which is the smaller distance from MIN or MAX to add to number used to sort results.
I thought i can use LEAST, but sql 2005 does not support it.


SELECT grade
FROM compositions
WHERE NOT ($a_value BETWEEN a_min AND a_max)
	or NOT ($b_value BETWEEN b_min AND b_max)
	or NOT ($c_value BETWEEN c_min AND c_max)
ORDER     
    BY LEAST(ABS(a_min - $a_value),ABS(a_max - $a_value)) 
     + LEAST(ABS(b_min - $b_value),ABS(b_max - $b_value))  
     + LEAST(ABS(c_min - $c_value),ABS(c_max - $c_value)) ASC

How to re-write this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 04:42:02

[code]-- Peso 1
SELECT TOP 1 Grade
FROM @Compositions
ORDER BY CASE
WHEN ABS(a_min - @a) < ABS(a_max - @a) THEN ABS(a_min - @a)
ELSE ABS(a_max - @a)
END
+ CASE
WHEN ABS(b_min - @b) < ABS(b_max - @b) THEN ABS(b_min - @b)
ELSE ABS(b_max - @b)
END
+ CASE
WHEN ABS(c_min - @c) < ABS(c_max - @c) THEN ABS(c_min - @c)
ELSE ABS(c_max - @c)
END

-- Peso 2
SELECT TOP 1 Grade
FROM @Compositions
ORDER BY ((ABS(a_min - @a) + ABS(a_max - @a)) / 2 - @a)
+ ((ABS(b_min - @b) + ABS(b_max - @b)) / 2 - @b)
+ ((ABS(c_min - @c) + ABS(c_max - @c)) / 2 - @c)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources