Using SQL Server 2012, I have a classification table that is something like this:
prodName class1min class1max class2min class2max class3min class3max
---------------------------------------------------------------------------------
prod A 1.5 1.8 1.8 2.1 2.1 2.5
prod B 3.6 3.9 3.9 4.5 4.5 5.6
prod C 2.7 3.2 3.2 3.6 3.6 4.2
etc...
Given a product name and a value, I need to find what classification (1, 2 or 3) my value belongs in.
ie. I have a product B item with a value of 4.1. I just need to be able to identify that it belongs in class 2.
So far I have experimented with creating unpivot tables based on what product is selected, like this:
Prod B:
class value
------------------
class1min 3.6
class1max 3.9
class2min 3.9
class2max 4.5
class3min 4.5
class3max 5.6
Then by inserting my value and sorting, I'm at least able to visualize the classification.
class value
------------------
class1min 3.6
class1max 3.9
class2min 3.9
myvalue 4.1
class2max 4.5
class3min 4.5
class3max 5.6
I'm still having problems isolating it by code though, and I think there might be a better way.
NOTE: in the case of a tie, I'd like the lower classification.
You can unpivot the data and then perform the comparison. Since you are using SQL Server 2012, then you can easily unpivot the min/max columns in sets using CROSS APPLY:
select prodname, class, [min], [max]
from yourtable
cross apply
(
values
('class1', class1min, class1max),
('class2', class2min, class2max),
('class3', class3min, class3max)
) c(class, [min], [max])
See Demo. Once the data has been unpivoted, then you can compare your value to find the class. If you have more classifications, then you can easily add more values in the subquery:
DECLARE @Prod VARCHAR(32) = 'Prod B',
@val DECIMAL(10,2) = 4.1;
select prodname, class, [min], [max]
from yourtable
cross apply
(
values
('class1', class1min, class1max),
('class2', class2min, class2max),
('class3', class3min, class3max)
) c(class, [min], [max])
where prodname = @Prod
and @val > [min]
and @val <= [max]