SQL Query to return an item within range or nearest range

Question

I have a table of ranges that looks like

CREATE TABLE [dbo].[WeightRange](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NULL,
    [LowerBound] [decimal](18, 2) NULL,
    [UpperBound] [decimal](18, 2) NULL,
    [GroupID] [int] NULL
)

Given a weight and group id I need to find the matching (or nearest) range id.

Example

WeightRanges
1, 0-100kgs,      0,  100, 1
2, 101-250kgs,  101,  250, 1
3, 501-1000kgs, 501, 1000, 1

If the weight is 10 the it should return id 1, if the weight is 1500 it should return id 3, and if the weight is 255 it should return id 2. I have left the group out of the example for simplicity.

At this stage I don't really want to change the database design.

Answer 1

I think this stored function should to the trick - it uses a CTE (Common Table Expression) internally, so it'll work with SQL Server 2005 and up:

CREATE FUNCTION dbo.FindClosestID(@WeightValue DECIMAL(17,2))
RETURNS INT
AS BEGIN
    DECLARE @ReturnID INT;

    WITH WeightDistance AS 
    (
     SELECT ID, ABS(Lowerbound - @WeightValue) 'Distance'
     FROM WeightRange
     UNION ALL
     SELECT ID, ABS(upperbound - @WeightValue) 'Distance'
     FROM WeightRange
    )
    SELECT TOP 1 @ReturnID = ID 
    FROM WeightDistance 
    ORDER BY Distance 

    RETURN @ReturnID
END

These queries will return the following values:

SELECT 
   dbo.FindClosestID(75.0), 
   dbo.FindClosestID(300.0), 
   dbo.FindClosestID(380.0), 
   dbo.FindClosestID(525.0), 
   dbo.FindClosestID(1500.0)

1        2      3       3         3

Marc

Answer 2

I'd use a CASE statement to create a column with the "distance", and then order by distance and take the first item.

Snippet which may help:

SELECT TOP 1 d.id
  FROM (
        SELECT id, CASE WHEN (@weight >= LowerBound)
                             AND (@weight <= UpperBound) THEN 0
                        WHEN (@weight < LowerBound) THEN LowerBound-@weight
                        WHEN (@weight > UpperBound) THEN @weight-UpperBound
                   END AS distance
          FROM WeightRange
       ) d
  WHERE d.distance IS NOT NULL
  ORDER BY d.distance ASC