SQL Server Performance

SQL Question

Discussion in 'Getting Started' started by lcerni, Jun 17, 2010.

  1. lcerni New Member

    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.
  2. Adriaan New Member

    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.
  3. lcerni New Member

    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
  4. Adriaan New Member

    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.

Share This Page