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

  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',  '>11' ,     17.00 )
,case when itemprice IN (Select itemrpice from @Exceptions) Then 
--compare the quantities to gather the pricing info
FROM @Parent
  • I formatted your code a bit. Just to be sure, the prices in @Parent are supposed to be 0.00 for rr11? – Erik Darling Apr 6 '17 at 19:50
  • @sp_BlitzErik - thank you for the cleanup. Yes, the price is 0.00 since the price varies based off the qty ordered. – BellHopByDayAmetuerCoderByNigh Apr 6 '17 at 19:57
  • If qtyordered cannot be split into two columns as recommended for some reason, then we would need to know the possible operators to allow for; would it be limited to range (1-5), greater than (>11)? what about less than (<5), or anything really strange? – RDFozz Apr 6 '17 at 19:58
  • Note - with the data as shown, if you order 11 "rr11"s, they're free! That might be something to consider checking for. Also - once you have your base solution, you might want to decide what to do if two conflicting rules were provided (for example, instead of the second exception being '6-10', what if it were '6-12')? – RDFozz Apr 6 '17 at 20:03
  • @RDFozz - thankfully I will be able to split qtyordered into a Min and Max column! – BellHopByDayAmetuerCoderByNigh Apr 7 '17 at 0:55

I would approach this using a SQL JOIN that joins both tables on the item, and the appropriate discount.

    ItemName varchar(100) NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
    , ItemQuantity int NOT NULL

    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 >= e.MinQuantityOrdered 
        AND p.ItemQuantity <= 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:

  1. 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.
  2. You are putting quotes around numeric values. Don't.

  3. 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.

  4. 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.

| improve this answer | |
  • Personally, I would go with the maximum value of int for MaxQuantityOrdered without an upper bound. It would seem unlikely for orders of 1,000,000 or more to happen, but there's no good reason not to allow for them. (of course, if the maximum value of an int in this SQL variant happens to be 999,999 - already done!) – RDFozz Apr 6 '17 at 19:55

You should break the quantity ordered into two columns or at least one integer column (but I like two). MinQuantity and MaxQuantity. Note I don't usually allow for abbreviations in tables or columns, but I'll make exceptions if it's universally accepted like Min and Max. Second, name your table appropriately, something like PriceBreak is more descriptive in my opinion than Exception (that could mean anything).

Once we have proper table names, column names and data types, then the solution becomes much easier. You will use the itemname and itemquantity to join to the proper row (itemquantity between minquantity and maxquantity). One of the reasons that it's a good idea to name everything well is that the code becomes self documenting and is easier to create and troubleshoot. In that scenario, you won't even need a case statement, just a LEFT JOIN and an ISNULL.

    itemname            varchar(100)
    ,itemprice          money
    ,QuantityOrdered    int

    itemname        varchar(100)
    ,MinQuantity    int
    ,MaxQuantity    int
    ,itemprice      money

INSERT INTO @Order (itemname, itemprice, QuantityOrdered) 
     ('rr11', '0.00', 10)
    ,('rr11', '0.00', 5)
    ,('rr11', '0.00', 22)
    ,('mc22', '15.00', 5)

INSERT INTO @PriceBreak (itemname, MinQuantity, MaxQuantity, itemprice) 
     ('rr11', 1,5, '15.50')
    ,('rr11', 6,10, '11.00')
    ,('rr11', 11,99999, '17.00')

    ,ISNULL(PB.ItemPrice, O.itemprice) ItemPrice
    ,ISNULL(PB.ItemPrice, O.itemprice) * QuantityOrdered
    @Order O
    LEFT JOIN @PriceBreak PB ON O.itemname = PB.itemname AND O.QuantityOrdered BETWEEN PB.MinQuantity and PB.MaxQuantity
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.