Find value from Table1 where value from Table2 is between rows in Table1

Question

Assume the following structure:

Items:

ItemId  Price
----------------
1000    129.95
2000    49.95
3000    159.95
4000    12.95

Thresholds:

PriceThreshold  Reserve
------------------------
19.95           10
100             5
150             1

-- PriceThreshold is the minimum price for that threshold/level

I'm using SQL Server 2008 to return the 'Reserve' based on where the item price falls between in 'PriceThreshold'.

Example:

ItemId  Reserve
1000    5
2000    10
3000    1

--Price for ItemId 4000 isn't greater than the lowest price threshold so should be excluded from the results.

Ideally I'd like to just be able to use some straight T-SQL, but if I need to create a stored procedure to create a temp table to store the values that would be fine.

Link to SQL Fiddle for schema

It's late and I think my brain shut off, so any help is appreciated.

Thanks.

Answer 1

Interested in something like this:

select
  ItemId, 
  (select top 1 Reserve
   from Threshold 
   where Threshold.PriceThreshold < Items.Price
    order by PriceThreshold desc) as Reserve
from
  Items
where
  Price > (select min(PriceThreshold) from Threshold)

SQLFiddle