locked
Tier Quantities RRS feed

  • Question

  • Hi,

    Using SQL Server 2008 R2.  Have the following (example) DDL and Sample Data:

    CREATE TABLE [dbo].[Prices]
    (
        [PriceID]   [varchar](50)   NULL,
        [ListID]    [varchar](50)   NULL,
        [ProductID] [varchar](50)   NULL,
        [SizeID]    [varchar](50)   NULL,
        [AreaID]    [varchar](50)   NULL,
        [Quantity]  [numeric](18,2) NULL,
        [Price]     [decimal](18,2) NULL,
        CONSTRAINT [PK_Prices_PriceID] PRIMARY KEY CLUSTERED ([PriceID] ASC)
        WITH
            (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
            )
        ON [PRIMARY]
        )
    ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Prices]
        ([PriceID], [ListID], [ProductID], [SizeID], [AreaID], [Quantity], [Price])
    VALUES
        (1, 'L1', 'P1', 'S1', 'A1', 10, 20),
        (2, 'L1', 'P1', 'S1', 'A1', 20, 17),
        (3, 'L1', 'P1', 'S1', 'A1', 99, 15),
        (4, 'L1', 'P2', 'S1', 'A1', 12, 45),
        (5, 'L1', 'P2', 'S1', 'A1', 24, 30)

    I need to return all prices matching a combination of ListID, ProductID, SizeID and AreaID, ordered by Quantity ASC (highest quantity without going over @value), as well as the Calculated Quantity (running remaining @value) to be used for that price.  So for example (assuming a @value of 25):

    ListID | ProductID | SizeID | AreaID | Quantity | CalcQty | Price
    L1     | P1        | S1     | A1     | 10       | 10      | 20
    L1     | P1        | S1     | A1     | 20       | 10      | 17
    L1     | P1        | S1     | A1     | 99       | 5       | 15

    The caveat is that (using the first example row) if @value is less than the smallest Quantity specified for that price combination, return @value as CalcQty.  Conversely, if @value is more than the largest Quantity, use the remaining @value (as following the last example row there would be a remaining @value of 5, whereas the Quantity for the row is 99).  I can't seem to wrap my head around this in a way that accounts for all scenarios.

    Any help is greatly appreciated!

    Best Regards

    Brad


    • Edited by 2012S4 Wednesday, January 27, 2016 4:22 PM Corrected Sample Result
    Wednesday, January 27, 2016 3:37 PM

Answers

  • I made a few adjustments, including adding a row to catch over-values:

    DECLARE @Prices TABLE (PriceID VARCHAR(50) NULL, ListID VARCHAR(50) NULL, ProductID VARCHAR(50) NULL, SizeID VARCHAR(50) NULL,
                           AreaID VARCHAR(50) NULL, Quantity NUMERIC(18,2) NULL, Price DECIMAL(18,2) NULL)
    
    INSERT INTO @Prices (PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price) VALUES
    (1, 'L1', 'P1', 'S1', 'A1', 10, 20),
    (2, 'L1', 'P1', 'S1', 'A1', 20, 17),
    (3, 'L1', 'P1', 'S1', 'A1', 99, 15),
    (6, 'L1', 'P1', 'S1', 'A1', 1000, 14),
    (4, 'L1', 'P2', 'S1', 'A1', 12, 45),
    (5, 'L1', 'P2', 'S1', 'A1', 24, 30)
    
    DECLARE @purchaseAmount INT = 100, @productID CHAR(2) = 'P1'
    
    ;WITH base AS (
    SELECT *, @purchaseAmount AS ThisQty, ROW_NUMBER() OVER (ORDER BY quantity) AS seq
      FROM @Prices
     WHERE ProductID = @productID
    ), rCTE AS (
    SELECT PriceID,  ListID,    ProductID,   SizeID,   AreaID,   Quantity,   Price,    seq,  
           CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) AS thisQty, 
    	   CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) * price AS thisMoney, 
    	   CAST(thisQty-quantity AS NUMERIC(18,2)) AS remainingQty
      FROM base
     WHERE seq = 1
    UNION ALL
    SELECT a.PriceID, a.ListID, a.ProductID, a.SizeID, a.AreaID, a.quantity, a.price, a.seq, 
           CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) AS thisQty, 
    	   CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) * a.price AS thisMOney, 
    	   CASE WHEN CAST(r.remainingQty-((a.quantity-r.quantity)) AS NUMERIC(18,2)) <= 0 THEN 0 ELSE CAST(r.remainingQty-((a.quantity-r.quantity)) AS NUMERIC(18,2)) END AS remainingQty
      FROM rCTE r
        INNER JOIN base a
    	  ON r.ProductID = a.ProductID
    	  AND r.seq + 1 = a.seq
    )
    
    
    SELECT PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price, thisQty, thisMoney, remainingQty
      FROM rCTE


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by Eric__Zhang Monday, February 15, 2016 8:26 AM
    Thursday, January 28, 2016 2:30 PM

All replies

  • Your logic is not clear, I also don't see the connection of your desired result to the sample code, e.g. why occurs the combination

    L1     | P1        | S1     | A1     | 10

    twice?

    Wednesday, January 27, 2016 4:12 PM
  • Hi Stefan,

    Have corrected my sample result.  Hopefully this illustrates things better.  Thanks for letting me know about that!

    Best Regards

    Brad

    Wednesday, January 27, 2016 4:23 PM
  • hmm, still don't get it. Qty 99 is greater then 25? Also how is your CalcQty defined? And why exactly are PriceID 4 and 5 excluded?
    Wednesday, January 27, 2016 5:02 PM
  • Hi Stefan,

    PriceID 4 and 5 are excluded because they don't match the price combination (ProductID not P1).  As far as the Quantity of 99:

    If I buy 3 hammers, I pay $60 (3 x $20) because the first Quantity Tier is 10, and I haven't exceeded it.  In this case, this row would have a Calculated Quantity of 3.

    If I buy 10 hammers, I pay $200 (10 x $20) because the first Quantity Tier is 10, and I haven't exceeded it.  In this case this row would have a Calculated Quantity of 10.

    If I buy 15 hammers, I pay $285 ((10 x $20) + (5 x $17)) because I've exceeded the first Quantity Tier of 10 but not the second Quantity Tier of 20.  In this case the first row would have a Calculated Quantity of 10, and the second row would have a Calculated Quantity of 5.

    If I buy 25 hammers, I pay $445 ((10 x $20) + (10 x $17) + (5 x $15)) because I've exceeded the first and second Quantity Tiers of 10 and 20 respectively but not the third Quantity Tier of 99.  In this case the first row would have a Calculated Quantity of 10, the second row would have a Calculated Quantity of 10, and the third row would have a Calculated Quantity of 5.

    If I buy 100 hammers, I pay $1570 ((10 x $20) + (10 x $17) + (80 x $15)) because I've exceeded the third Quantity Tier of 99, and there isn't any higher tier.  In this case the first row would have a Calculated Quantity of 10, the second row would have a Calculated Quantity of 10, and the third row would have a Calculated Quantity of 80.

    Hopefully this sheds some light!



    • Edited by 2012S4 Wednesday, January 27, 2016 9:06 PM Calculated Quantity Figure Updates
    Wednesday, January 27, 2016 5:26 PM
  • Something like this?

    DECLARE @Prices TABLE (PriceID VARCHAR(50) NULL, ListID VARCHAR(50) NULL, ProductID VARCHAR(50) NULL, SizeID VARCHAR(50) NULL,
                           AreaID VARCHAR(50) NULL, Quantity NUMERIC(18,2) NULL, Price DECIMAL(18,2) NULL)
    
    INSERT INTO @Prices (PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price) VALUES
    (1, 'L1', 'P1', 'S1', 'A1', 10, 20),
    (2, 'L1', 'P1', 'S1', 'A1', 20, 17),
    (3, 'L1', 'P1', 'S1', 'A1', 99, 15),
    (4, 'L1', 'P2', 'S1', 'A1', 12, 45),
    (5, 'L1', 'P2', 'S1', 'A1', 24, 30)
    
    DECLARE @purchaseAmount INT = 98, @productID CHAR(2) = 'P1'
    
    SELECT TOP 1 *
      FROM @Prices
     WHERE Quantity  < @purchaseAmount
       AND ProductID = @productID
     ORDER BY Quantity DESC


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, January 27, 2016 6:00 PM
  • Hi Patrick,

    Thanks for the reply.  Unfortunately this doesn't meet my requirements.  If you take a look at my last reply to Stefan it provides some more insight on exactly what I'm looking for and how it should work.  Appreciate you taking a swing at it though!

    Best Regards

    Brad

    Wednesday, January 27, 2016 6:13 PM
  • I see. If a order fills a previous tier you get that quantity at the first tier price, not all items at the final tier price.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, January 27, 2016 10:05 PM
  • Try this:

    DECLARE @Prices TABLE (PriceID VARCHAR(50) NULL, ListID VARCHAR(50) NULL, ProductID VARCHAR(50) NULL, SizeID VARCHAR(50) NULL,
                           AreaID VARCHAR(50) NULL, Quantity NUMERIC(18,2) NULL, Price DECIMAL(18,2) NULL)
    
    INSERT INTO @Prices (PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price) VALUES
    (1, 'L1', 'P1', 'S1', 'A1', 10, 20),
    (2, 'L1', 'P1', 'S1', 'A1', 20, 17),
    (3, 'L1', 'P1', 'S1', 'A1', 99, 15),
    (4, 'L1', 'P2', 'S1', 'A1', 12, 45),
    (5, 'L1', 'P2', 'S1', 'A1', 24, 30)
    
    DECLARE @purchaseAmount INT = 50, @productID CHAR(2) = 'P1'
    
    ;WITH base AS (
    SELECT *, @purchaseAmount AS ThisQty, ROW_NUMBER() OVER (ORDER BY quantity) AS seq
      FROM @Prices
     WHERE ProductID = @productID
    ), rCTE AS (
    SELECT PriceID,  ListID,    ProductID,   SizeID,   AreaID,   Quantity,   Price,    seq,  CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) AS thisQty, CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) * price AS thisMoney, thisQty-quantity AS remainingQty
      FROM base
     WHERE seq = 1
    UNION ALL
    SELECT a.PriceID, a.ListID, a.ProductID, a.SizeID, a.AreaID, a.quantity, a.price, a.seq, CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) AS thisQty, CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) * a.price AS thisMOney, a.thisQty-a.quantity AS remainingQty
      FROM rCTE r
        INNER JOIN base a
    	  ON r.ProductID = a.ProductID
    	  AND r.seq + 1 = a.seq
     WHERE a.thisQty > 0
    )
    
    
    SELECT PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price, thisQty, thisMoney
      FROM rCTE
    PriceID	ListID	ProductID	SizeID	AreaID	Quantity	Price	thisQty	thisMoney
    1		L1		P1			S1		A1		10.00	20.00	10		200.00
    2		L1		P1			S1		A1		20.00	17.00	10		170.00
    3		L1		P1			S1		A1		99.00	15.00	30		450.00


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Wednesday, January 27, 2016 10:54 PM
  • Hi Patrick,

    Thanks for the reply.  Your solution seems to work in all but one case: when the supplied quantity is higher than the highest Quantity for that price combination, the thisQty value stays at the remainingQty calculated for the tier.  For example:

    @purchaseAmount = 100

    Shows Row 1's [thisQty] as 10, Row 2's [thisQty] as 10, Row 3's [thisQty] as 79, which is short by 1.  However, adjusting @purchaseAmount to a higher value keeps Row 3's [thisQty] value at 79.  In a case like this, I'd want Row 3's [UnitPrice] used for the RemainingQuantity up to (and over) the row's Quantity.

    Thanks!

    Wednesday, January 27, 2016 11:37 PM
  • eg for 25, why 10,10,5 but not 10,9,6 or 10,11,4, how is the quantity for each tier calculated?
    Thursday, January 28, 2016 6:46 AM
  • I made a few adjustments, including adding a row to catch over-values:

    DECLARE @Prices TABLE (PriceID VARCHAR(50) NULL, ListID VARCHAR(50) NULL, ProductID VARCHAR(50) NULL, SizeID VARCHAR(50) NULL,
                           AreaID VARCHAR(50) NULL, Quantity NUMERIC(18,2) NULL, Price DECIMAL(18,2) NULL)
    
    INSERT INTO @Prices (PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price) VALUES
    (1, 'L1', 'P1', 'S1', 'A1', 10, 20),
    (2, 'L1', 'P1', 'S1', 'A1', 20, 17),
    (3, 'L1', 'P1', 'S1', 'A1', 99, 15),
    (6, 'L1', 'P1', 'S1', 'A1', 1000, 14),
    (4, 'L1', 'P2', 'S1', 'A1', 12, 45),
    (5, 'L1', 'P2', 'S1', 'A1', 24, 30)
    
    DECLARE @purchaseAmount INT = 100, @productID CHAR(2) = 'P1'
    
    ;WITH base AS (
    SELECT *, @purchaseAmount AS ThisQty, ROW_NUMBER() OVER (ORDER BY quantity) AS seq
      FROM @Prices
     WHERE ProductID = @productID
    ), rCTE AS (
    SELECT PriceID,  ListID,    ProductID,   SizeID,   AreaID,   Quantity,   Price,    seq,  
           CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) AS thisQty, 
    	   CAST(CASE WHEN quantity > thisQty THEN thisQty ELSE quantity END AS INT) * price AS thisMoney, 
    	   CAST(thisQty-quantity AS NUMERIC(18,2)) AS remainingQty
      FROM base
     WHERE seq = 1
    UNION ALL
    SELECT a.PriceID, a.ListID, a.ProductID, a.SizeID, a.AreaID, a.quantity, a.price, a.seq, 
           CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) AS thisQty, 
    	   CAST(CASE WHEN (a.quantity-r.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantity-r.quantity) END AS INT) * a.price AS thisMOney, 
    	   CASE WHEN CAST(r.remainingQty-((a.quantity-r.quantity)) AS NUMERIC(18,2)) <= 0 THEN 0 ELSE CAST(r.remainingQty-((a.quantity-r.quantity)) AS NUMERIC(18,2)) END AS remainingQty
      FROM rCTE r
        INNER JOIN base a
    	  ON r.ProductID = a.ProductID
    	  AND r.seq + 1 = a.seq
    )
    
    
    SELECT PriceID, ListID, ProductID, SizeID, AreaID, Quantity, Price, thisQty, thisMoney, remainingQty
      FROM rCTE


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by Eric__Zhang Monday, February 15, 2016 8:26 AM
    Thursday, January 28, 2016 2:30 PM