Here is an example of my table. LBound and UBound are numeric where Label is a varchar. LBound UBound Label 0.00 0.25 <.25 0.25 0.50 .25 to .5 0.50 1.50 .5 to 1.5 1.50 2.50 1.5 to 2.5 2.50 3.50 2.5 to 3.5 3.50 4.50 3.5 to 4.5 Is there a way, such as a function, that will take a number, lets say .6, and spits out the label as [.5 to 1.5]? And if the input was 2.6 then the output would be [2.5 to 3.5]? I have been requested to convert a Excel spreadsheet into using SQL Server. In one column there is a value of .6 and they use a VLOOKUP to pick up the label. I have no key to join the two tables together thus I am confused at this point.
Look up CASE expressions in BOL. People coming from VBA often confuse the separate T-SQL syntax items SELECT and CASE with the "Select Case" syntax from VBA, which is control-of-flow, whereas CASE is an in-line expression. It's kind of like the IIF() function from VBA, but with WHEN, THEN, ELSE and END instead of commas and brackets.
This was what I was looking for and I found it... CREATE FUNCTION dbo.udf_Lookup ( @Label VARCHAR(50) --code to return ) Returns VARCHAR(50) AS BEGIN DECLARE @Result VARCHAR(50) SELECT TOP 1 @Result = column FROM table WHERE @Label >= LowerBound AND @Label < UpperBound RETURN @Result END
Since you have a table where the labels are stored, you could also have JOINed with a BETWEEN expression in the ON statement. Otherwise, you could have had an expression like SELECT mytbl.mycol, CASE WHEN mytbl.mycol BETWEEN 0.00 AND 0.24 THEN '< .25' WHEN mytbl.mycol BETWEEN 0.25 AND 0.49 THEN '.25 TO .5' ......... END FROM mytbl ... or you might have translated this to a UDF, without having to use an actual lookup table. You might also apply some clever calculation with modulo and stuff like that.
