Sql Query for Commissions (Decision?) Table

Question

SQL 2008

I have a commission table that looks like this:

Comm% | ProfitStartRange | ProfitEndRange

0.01 | 0.00 | 100.99
0.02 | 101  | 500.99
0.03 | 501  | 1000.99

etc...

Basically I want create a query that returns the appropriate Comm% based on a value. I would like to do this inline and not in a user defined function if possible as I will be calculating a large set.

Answer 1

SELECT comm from yourtable 
  where profit BETWEEN yourtable.ProfitStartRange and yourtable.ProfitEndRange