Store color for data ranges and use in case statement in SQL Server

Question

I have a data set as below. Fiddle URL

I need to get the color of given number using operators in above table.

For example;

When I called the function getColor(6), should return PURPLE.

When I called the function getColor(0), should return RED.

How could I achieve this?

Answer 1

The simplest solution is to create matching condition for each operator using CASE expressions:

DECLARE @val NUMERIC(10) = 6;

SELECT /* TOP 1 */ *
FROM #range_color
CROSS APPLY (SELECT @val - range_number) AS ca(diff)
WHERE CASE WHEN diff <  0 AND range_operator = '<'  THEN 1 END = 1
OR    CASE WHEN diff <= 0 AND range_operator = '<=' THEN 1 END = 1
OR    CASE WHEN diff >= 0 AND range_operator = '>=' THEN 1 END = 1
OR    CASE WHEN diff >  0 AND range_operator = '>'  THEN 1 END = 1
ORDER BY ABS(diff)

Answer 2

Request you to have a look into this below query.

declare @PassingValues int = 0
declare @MatchingRange int

select top 1 @MatchingRange = range_number from #RangeColor where range_number = @PassingValues
order by range_color_id     
if(@@ROWCOUNT <= 0)
begin
if(@MatchingRange is not null)
begin
    select top 1 * from #RangeColor where range_number <= @MatchingRange 
    order by range_color_id
end
else
begin
    Select top 1 @MatchingRange = range_number from #RangeColor where range_number < @PassingValues 
    order by range_number desc      
    if(@MatchingRange IS NULL)
    begin
        select top 1 @MatchingRange = range_number from #RangeColor where range_number > @PassingValues 
        order by range_number                   
        select top 1 * from #RangeColor where range_number > @MatchingRange 
        order by range_color_id
    end
    else
    begin
        select top 1 * from #RangeColor where range_number <= @MatchingRange 
        order by range_color_id desc        
    end
end
end
else
begin
    select top 1 * from #RangeColor where range_number = @PassingValues
    order by range_color_id     
end