Answered by:
Tier Quantities
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 overvalues:
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(thisQtyquantity 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.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) AS thisQty, CAST(CASE WHEN (a.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) * a.price AS thisMOney, CASE WHEN CAST(r.remainingQty((a.quantityr.quantity)) AS NUMERIC(18,2)) <= 0 THEN 0 ELSE CAST(r.remainingQty((a.quantityr.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, thisQtyquantity 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.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) AS thisQty, CAST(CASE WHEN (a.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) * a.price AS thisMOney, a.thisQtya.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. Edited by Patrick Hurst Wednesday, January 27, 2016 10:54 PM
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 overvalues:
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(thisQtyquantity 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.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) AS thisQty, CAST(CASE WHEN (a.quantityr.quantity) > r.remainingQty THEN r.remainingQty ELSE (a.quantityr.quantity) END AS INT) * a.price AS thisMOney, CASE WHEN CAST(r.remainingQty((a.quantityr.quantity)) AS NUMERIC(18,2)) <= 0 THEN 0 ELSE CAST(r.remainingQty((a.quantityr.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