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)