VLOOKUP-style range lookup in T-SQL

Question

Here's a tricky problem I haven't quite been able to get my head around. I'm using SQL Server 2008, and I have a sparse range table that looks like this:

Range     Profession
-----     ----------
0         Office Worker
23        Construction
54        Medical

Then I have another table with values that are within these ranges. I'd like to construct a query which joins these two tables and gives me the Profession value that is less than or equal to the given value. So let's say my other table looks like this:

Value
29
1
60

Then I'd like my join to return:

Value     Profession
-----     ----------
29        Construction
1         Office Worker
60        Medical

(because 29>the 23 for Construction but <=the 54 for Medical)

Is there any way I can get SQL to bend to my will in this manner, short of actually blowing out the range table to include every possible value?

Thank you.

Answer 1

Easist Way to do this is to add a another column to you sparse range table.

LowRange       HighRange      Profession
   0              22         Office Worker
  23              53          Construction
  54            999999         Medical

Then use a query like this to get the range(table 2 is the one with the 29,1,60 values):

SELECT Table_2.JoinKey as Value, Table_1.Description as Profession 
  FROM Table_1 INNER JOIN Table_2 
          ON Table_2.JoinKey => Table_1.LowRangeKey 
             AND Table_2.JoinKey <= Table_1.HighRangeKey;

Answer 2

You could use CROSS APPLY:

select v.Value, p.Profession
from tblValues v
cross apply
   (select top(1) pr.Profession
    from tblProfessionRanges pr
    where pr.Range <= v.Value ORDER BY pr.[Range] DESC) p

It should be faster than using max and doesn't need a max-range do be maintained.