In T-SQL is there a built-in command to determine if a number is in a range from another table

Question

This is not a homework question.

I'm trying to take the count of t-shirts in an order and see which price range the shirts fall into, depending on how many have been ordered. My initial thought (I am brand new at this) was to ask another table if count > 1st price range's maximum, and if so, keep looking until it's not.

printing_range_max  printing_price_by_range
15                  4
24                  3
33                  2

So for example here, if the order count is 30 shirts they would be $2 each.

When I'm looking into how to do that, it looks like most people are using BETWEEN or IF and hard-coding the ranges instead of looking in another table. I imagine in a business setting it's best to be able to leave the range in its own table so it can be changed more easily. Is there a good/built-in way to do this or should I just write it in with a BETWEEN command or IF statements?

EDIT: SQL Server 2014

Answer 1

Let's say we have this table:

DECLARE @priceRanges TABLE(printing_range_max tinyint, printing_price_by_range tinyint);
INSERT @priceRanges VALUES (15, 4), (24, 3), (33, 2);

You can create a table with ranges that represent the correct price. Below is how you would do this in pre-2012 and post-2012 systems:

DECLARE @priceRanges TABLE(printing_range_max tinyint, printing_price_by_range tinyint);
INSERT @priceRanges VALUES (15, 4), (24, 3), (33, 2);

-- post-2012 using LAG
WITH pricerange AS
(
  SELECT 
    printing_range_min = LAG(printing_range_max, 1, 0) OVER (ORDER BY printing_range_max),
    printing_range_max, 
    printing_price_by_range  
  FROM @priceRanges
)
SELECT * FROM pricerange;

-- pre-2012 using ROW_NUMBER and a self-join
WITH prices AS
(
  SELECT 
    rn = ROW_NUMBER() OVER (ORDER BY printing_range_max),
    printing_range_max, 
    printing_price_by_range  
  FROM @priceRanges
),
pricerange As
(
  SELECT 
    printing_range_min = ISNULL(p2.printing_range_max, 0),
    printing_range_max = p1.printing_range_max,
    p1.printing_price_by_range
  FROM prices p1
  LEFT JOIN prices p2 ON p1.rn = p2.rn+1
)
SELECT * FROM pricerange;

Both queries return:

printing_range_min printing_range_max printing_price_by_range
------------------ ------------------ -----------------------
0                  15                 4
15                 24                 3
24                 33                 2

Now that you have that you can use BETWEEN for your join. Here's the full solution:

-- Sample data
DECLARE @priceRanges TABLE
(
  printing_range_max tinyint, 
  printing_price_by_range tinyint
  -- if you're on 2014+
  ,INDEX ix_xxx NONCLUSTERED(printing_range_max, printing_price_by_range)
  -- note: second column should be an INCLUDE but not supported in table variables
);
DECLARE @orders TABLE 
(
  orderid int identity, 
  ordercount int
  -- if you're on 2014+ 
  ,INDEX ix_xxy NONCLUSTERED(orderid, ordercount)
  -- note: second column should be an INCLUDE but not supported in table variables
);

INSERT @priceRanges VALUES (15, 4), (24, 3), (33, 2);
INSERT @orders(ordercount) VALUES (10), (20), (25), (30);


-- Solution:
WITH pricerange AS
(
  SELECT 
    printing_range_min = LAG(printing_range_max, 1, 0) OVER (ORDER BY printing_range_max),
    printing_range_max, 
    printing_price_by_range  
  FROM @priceRanges
)
SELECT 
  o.orderid,
  o.ordercount,
  --p.printing_range_min, 
  --p.printing_range_max
  p.printing_price_by_range
FROM pricerange p
JOIN @orders o ON o.ordercount BETWEEN printing_range_min AND printing_range_max

Results:

orderid     ordercount  printing_price_by_range
----------- ----------- -----------------------
1           10          4
2           20          3
3           25          2
4           30          2

Now that we have that we can