locked
Calculate price for quantity RRS feed

  • Question

  •  

    Hello all, I have an interesting question about calculating a price in my database for a quantity entered.  I have a join table that I store ProductID, SizeID, Quantity and Price.  The price for a particular product changes based most often on the quantity ordered.  For example, if you order one unit of a widget the price is 10.00, however if you order one dozen units of the same widget the price drop to 9.75 and so on.

     

    Here is a sample of my table....columns are in the order I specified above.

     

    1, 1, 1, 10.00
    1, 2, 1, 10.00
    1, 3, 1, 10.00
    1, 1, 6, 9.90
    1, 2, 6, 9.90
    1, 3, 6, 9.90
    1, 1, 12, 9.75
    1, 2, 12, 9.75
    1, 3, 12, 9.75
    1, 1, 24, 9.50
    1, 2, 24, 9.50
    1, 3, 24, 9.50
    1, 3, 36, 9.30
    1, 2, 36, 9.30
    1, 1, 36, 9.30

     

    So depending on if my widget is available in certain sizes, the second column, then each product has an price for the size id and quantity at which the price break occurs.

     

    I use this stored procedure to return the price or price range based on the input parameters entered.

     

    CREATE PROCEDURE dbo.sp_GetPrice
     @ProductID INT,
     @QuantityID INT = NULL,
     @SizeID INT = NULL
    AS
    BEGIN
     SET NOCOUNT ON

     IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED
      SELECT DISTINCT Price AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   Quantity = @QuantityID
     
     IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED
      SELECT Price AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   SizeID = @SizeID
      AND   Quantity = @QuantityID

     IF @SizeID IS NULL AND @QuantityID IS NULL
      SELECT MIN(Price) AS  'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT MAX(Price)
      FROM join_ProductSizeQuantityPrice
      WHERE ProductId = @ProductID 
     
    END
    GO

     

    So everything works great, however, when a user orders an quantity amount like 7, 13, 26 - which is not part of the table - I want to give them the discount available to them for the price break appropriate.

     

    For example if a customer orders 16 widgets of size 2 the price break threshold they have crossed is one dozen, however they have not yet reached the next one which is two dozen.  Therefore I want to offer the price associated with one dozen widgets of size id which is: $9.75.  Once I have this a simple calculation of this price * 16 units would give me a total but my question is, how do I elegantly design this quantity / right price per unit calculation?

     

    -Brian

     

    Wednesday, February 20, 2008 5:48 PM

Answers

  • I found this will work great....

     

    IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL 

      SELECT MIN(Price) AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   SizeID = @SizeID
      AND   Quantity <= @QuantityID

     

    Small changes....  :-)
    Wednesday, February 20, 2008 6:12 PM
  • Yes, that's correct.  If the implied assumption I was referring to is actually part of the business rules in your organization, then your query should work just fine.

     

    Ben Aminnia

     

    Wednesday, February 20, 2008 8:12 PM

All replies

  • I thik this may be it



    CREATE PROCEDURE dbo.sp_GetPrice
     @ProductID INT,
     @QuantityID INT = NULL,
     @SizeID INT = NULL
    AS
    BEGIN
     SET NOCOUNT ON

     IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED
      SELECT DISTINCT Price AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   Quantity <= @QuantityID AND @QuantityID < Quantity
     
     IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED
      SELECT Price AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   SizeID = @SizeID
      AND   Quantity <= @QuantityID AND @QuantityID < Quantity

     IF @SizeID IS NULL AND @QuantityID IS NULL
      SELECT MIN(Price) AS  'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT MAX(Price)
      FROM join_ProductSizeQuantityPrice
      WHERE ProductId = @ProductID
     
    END
    GO
    Wednesday, February 20, 2008 5:59 PM
  • I found this will work great....

     

    IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL 

      SELECT MIN(Price) AS  'Price'
      FROM join_ProductSizeQuantityPrice
      WHERE ProductID = @ProductID
      AND   SizeID = @SizeID
      AND   Quantity <= @QuantityID

     

    Small changes....  :-)
    Wednesday, February 20, 2008 6:12 PM
  • Try this:

     

    SELECT Price AS 'Price'

    FROM join_ProductSizeQuantityPrice

    WHERE ProductID = @ProductID

    AND SizeID = @SizeID

    and Quantity = (select MAX(Quantity) FROM join_ProductSizeQuantityPrice

    WHERE ProductID = @ProductID

    AND SizeID = @SizeID

    AND Quantity <= @QuantityID)

     

    Ben Aminnia

    Wednesday, February 20, 2008 6:36 PM
  • Ben, can you explain what benefits your approach might have over my last post?  Thanks. 

     

    Wednesday, February 20, 2008 7:25 PM
  • Good question!

     

    In your approach, there’s an implied assumption that a higher quantity always has a lower unit price and therefore, it looks for MIN(Price).  My approach doesn’t have that assumption and just looks for the price of highest quantity not exceeding requested quantity.

     

    I’m sure for 99.9% of cases, your assumption is valid, but I have actually seen cases that larger quantities do not necessarily have a lower price – and they may even have a higher price (for marketing, packaging, shipping, or other reasons).

     

    Ben Aminnia

     

    Wednesday, February 20, 2008 7:36 PM
  • Ok, that makes sense, however, if my business rules are that higher quantities will have more significant price breaks then using the subquery would not only be more time consuming and expensive, from a database processing perspective, but also not neccessary.  Correct?

     

    Wednesday, February 20, 2008 7:54 PM
  • Yes, that's correct.  If the implied assumption I was referring to is actually part of the business rules in your organization, then your query should work just fine.

     

    Ben Aminnia

     

    Wednesday, February 20, 2008 8:12 PM