Calculating Price Based Off Price Break Table I have an orders table and a price break table.  If an itemcode exists in the price break table then the price is reduced based on the quantity.  I am not sure of what the syntax would be for a conditional like this, so I am going to seek assistance.  This is sample DDL and a query that I started on with a case statement but not sure how to finish it off

 DECLARE @Parent TABLE
(
  itemname VARCHAR(100),
  itemprice FLOAT,
  itemqty INT
)

 DECLARE @Exceptions TABLE
(
  itemname VARCHAR(100),
  qtyordered VARCHAR(10),
  itemrpice FLOAT
)

 INSERT INTO @Parent ( itemname, itemprice, itemqty )
            VALUES  (  'rr11',   0.00 ,     10 ),
                    (  'rr11',   0.00 ,     5 ),
                    (  'rr11',   0.00 ,     22 ),
                    (  'mc22',   15.00,     5 )

 INSERT INTO @Exceptions ( itemname, qtyordered, itemrpice )
            VALUES      (  'rr11',  '1-5' ,     15.50 ),
                        (  'rr11',  '6-10',     11.00 ),
                        (  'rr11',  '&gt;11' ,     17.00 )
Select
itemname
,case when itemprice IN (Select itemrpice from @Exceptions) Then 
--compare the quantities to gather the pricing info
itemqty
FROM @Parent
I would approach this using a SQL JOIN that joins both tables on the item, and the appropriate discount.

DECLARE @Parent TABLE 
(
    ItemName varchar(100) NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
    , ItemQuantity int NOT NULL
);

DECLARE @Exceptions TABLE
(
    ItemName varchar(100) NOT NULL
    , MinQuantityOrdered int NOT NULL
    , MaxQuantityOrdered int NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
);

INSERT INTO @Parent (ItemName, ItemPrice, ItemQuantity) 
VALUES ('rr11', 999999.99, 10)
    , ('rr11', 999999.99, 5)
    , ('rr11', 999999.99, 22)
    , ('mc22', 15.00, 5)

INSERT INTO @Exceptions (ItemName, MinQuantityOrdered, MaxQuantityOrdered, ItemPrice) 
VALUES ('rr11', 1, 5, 15.50)
    , ('rr11', 6, 10, 11.00)
    , ('rr11', 11, 999999, 17.00);

SELECT p.ItemName
    , p.ItemQuantity
    , AdjustedPrice = ISNULL(e.ItemPrice, p.ItemPrice)
    , AdjustedTotal = p.ItemQuantity * ISNULL(e.ItemPrice, p.ItemPrice)
FROM @Parent p
    LEFT JOIN @Exceptions e ON p.itemname = e.itemname 
        AND p.ItemQuantity &gt;= e.MinQuantityOrdered 
        AND p.ItemQuantity &lt;= e.MaxQuantityOrdered;


The results:

+----------+--------------+---------------+---------------+
| ItemName | ItemQuantity | AdjustedPrice | AdjustedTotal |
+----------+--------------+---------------+---------------+
| rr11     |           10 |       11.0000 |      110.0000 |
| rr11     |            5 |       15.5000 |       77.5000 |
| rr11     |           22 |       17.0000 |      374.0000 |
| mc22     |            5 |       15.0000 |       75.0000 |
+----------+--------------+---------------+---------------+

You'll notice in the example above, I have arbitrarily chosen the value 999999.00 as the maximum price for item rr11 in the @Parent table.  That was only because you chose to use 0.00 for the default price of the item.  If you use 0.00 for a default price, and then forget to create an entry in the @Exceptions table for that item, people will buy as many of those items as they can order since they will be essentially free.  I used 999999 as the maximum quantity for a discount in the @Exceptions table since you need to choose some maximum number for the MaxQuantityOrdered column, and 999999 seems like a fairly high number of items to order.  If you believe you'll sell a million or more items in one order, by all means increase that.  Since the quantity columns are int data types, the maximum you could specify would be 2147483647, which is a lot.

A couple of nit-picky things I noticed:


Use the correct case, for both keywords, data types, and column names.  


keywords should be in ALL CAPS, that is how they are shown in the documentation.
data types should be lower-case since that is how they are listed in sys.types
column names should be camel case, or in the very least not alllowercapscrammedtogether.  Just for sanity of the reader.

You are putting quotes around numeric values.  Don't.  
You should use a numeric range for values that are numeric.  i.e. Don't use a character column like 0 - 15 for the range of quantities, use two columns, MinValue and MaxValue with 0 and 15.
This one is a bit subjective, and many people won't agree fully with me.  Don't use a char value such as ItemName as the key to join rows between tables unless you are certain those values will not change.  Use a surrogate integer key to uniquely identify each item.  Name it ItemID.  Then if you ever need to correct the spelling of an item, you won't need to update every reference to that item in the database.  Yes, you can use relational integrity to cascade those changes throughout all referenced tables, but if you just use a surrogate key from the start, you'll never have to worry about that.