Find Maximum Qty of fruits (regardless of fruit) which could be bought by $50
-
Find Maximum Qty of fruits (regardless of fruit) which could be bought by $50 accounting for the total qty available in each scenario. Final result query just need the qty
IF OBJECT_ID (N'tempdb..#TEMP1', N'U') IS NOT NULL DROP TABLE #TEMP1
CREATE TABLE #TEMP1
(
Product VARCHAR(20),
QtyAvailableToPurchase INT,
Price DECIMAL(10,2)
)
INSERT INTO #TEMP1 (Product,QtyAvailableToPurchase,Price)
VALUES ('Apple',15,3),
('Orange',15,5),
('Cherry',10,10)
SELECT * FROM #TEMP4Answer should be: 16
-
Maybe do the P*Q for the lowest priced fruits, sort that way, then do a running total. Filter for running total less than your threshold?
Why are you recreating tables all over the place? Why not just do it once? You're not adding anything to the discussion by recreating them 4 times.
-
Isn't Case # 1 answer = 16 ?
15 apples @ $3 = $45
Plus 1 orange @ $5 = $50
16 fruit total
(Expensive cherries !)
-
Thats correct - apologies - its 16 - updated the question
-
I was able to get this far
IF OBJECT_ID (N'tempdb..#Sol1', N'U') IS NOT NULL DROP TABLE #Sol1
SELECT *,
QtyAvailableToPurchase*Price [TotalAmount],
SUM(QtyAvailableToPurchase*Price) OVER (ORDER BY Price) [RunningTotal],
ROW_NUMBER() OVER (ORDER BY Price) RowNum
INTO #Sol1
FROM #TEMP1
ORDER BY Price
SELECT * FROM #Sol1 -
I suspect there's some way to do this without recursion, but for now, here's a method using recursion.
DECLARE @amount_to_spend decimal(9, 2)
SET @amount_to_spend = 50.00
;WITH cte_products_by_price AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY Price) AS price_rank
FROM #TEMP4
),
cte_determine_products_to_buy AS (
SELECT
@amount_to_spend AS amount_to_spend, row_amount_spent, row_amount_spent AS total_amount_spent,
price, price_rank, @amount_to_spend - row_amount_spent AS amount_remaining,
row_quantity_bought, row_quantity_bought AS total_quantity_bought
FROM cte_products_by_price pbp
CROSS APPLY (
SELECT CAST(CASE WHEN FLOOR(((@amount_to_spend) / pbp.Price)) = 0 THEN 0
WHEN FLOOR((@amount_to_spend) / pbp.Price) >= QtyAvailableToPurchase THEN QtyAvailableToPurchase
ELSE FLOOR((@amount_to_spend) / pbp.Price) END AS int) AS row_quantity_bought
) AS calc1
CROSS APPLY (
SELECT CAST(calc1.row_quantity_bought * price AS decimal(9, 2)) AS row_amount_spent
) AS calc2
WHERE price_rank = 1
UNION ALL
SELECT
@amount_to_spend AS amount_to_spend, calc2.row_amount_spent, CAST(dpt.total_amount_spent + calc2.row_amount_spent AS decimal(9, 2)) AS total_amount_spent,
pbp.price, pbp.price_rank, (@amount_to_spend - dpt.total_amount_spent),
calc1.row_quantity_bought, CAST(dpt.total_quantity_bought + calc1.row_quantity_bought AS int) AS total_quantity_bought
FROM cte_determine_products_to_buy dpt
INNER JOIN cte_products_by_price pbp ON pbp.price_rank = dpt.price_rank + 1
CROSS APPLY (
SELECT CAST(CASE WHEN FLOOR(((@amount_to_spend - dpt.total_amount_spent) / pbp.Price)) = 0 THEN 0
WHEN FLOOR((@amount_to_spend - dpt.total_amount_spent) / pbp.Price) >= QtyAvailableToPurchase THEN QtyAvailableToPurchase
ELSE FLOOR((@amount_to_spend - dpt.total_amount_spent) / pbp.Price) END AS int) AS row_quantity_bought
) AS calc1
CROSS APPLY (
SELECT CAST(calc1.row_quantity_bought * pbp.price AS decimal(9, 2)) AS row_amount_spent
) AS calc2
)
SELECT MAX(total_quantity_bought) AS total_quantity_bought
FROM cte_determine_products_to_buySQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
;with cte as
(
select sum(t.price) over (order by t.price asc, v.N asc) RunningTotal, *
from #TEMP1 t
cross apply dbo.fnTally(1, t.QtyAvailableToPurchase) v
where v.N * t.price <= 50
)
select count(*)
from cte
where RunningTotal <= 50;https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
-
I may have something wrong with my understanding of the problem but, according to the values you've provided, there is no combination of fruits in case #1 where you can get a Qty of 31 and be under $50. The max Qty for that example would be 16.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
I may have something wrong with my understanding of the problem but, according to the values you've provided, there is no combination of fruits in case #1 where you can get a Qty of 31 and be under $50. The max Qty for that example would be 16.
Ah... I see I was "camped out" for too long on the thread and you corrected Case 1 to be 16.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks everyone. solution provided above works.
- Jonathan AC Roberts wrote:
;with cte as
(
select sum(t.price) over (order by t.price asc, v.N) RunningTotal, *
from #TEMP1 t
cross apply dbo.fnTally(1, t.QtyAvailableToPurchase) v
where v.N * price <= 50
)
select count(*)
from cte
where RunningTotal <= 50;https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
Awesome try but try it with these values and see why the ORDER BY needs one more attribute.
DROP TABLE IF EXISTS #Temp1;
CREATE TABLE #Temp1
(
Product VARCHAR(20)
,QtyAvailableToPurchase INT
,Price DECIMAL(10,2)
)
;
INSERT INTO #TEMP1
(Product,QtyAvailableToPurchase,Price)
VALUES ('Orange',10,3)
,('Cherry',10,3)
,('Apple', 10,3)
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jonathan AC Roberts wrote:
;with cte as
(
select sum(t.price) over (order by t.price asc, v.N) RunningTotal, *
from #TEMP1 t
cross apply dbo.fnTally(1, t.QtyAvailableToPurchase) v
where v.N * price <= 50
)
select count(*)
from cte
where RunningTotal <= 50;https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
Awesome try but try it with these values and see why the ORDER BY needs one more attribute.
DROP TABLE IF EXISTS #Temp1;
CREATE TABLE #Temp1
(
Product VARCHAR(20)
,QtyAvailableToPurchase INT
,Price DECIMAL(10,2)
)
;
INSERT INTO #TEMP1
(Product,QtyAvailableToPurchase,Price)
VALUES ('Orange',10,3)
,('Cherry',10,3)
,('Apple', 10,3)
;Thanks Jeff, well spotted, I totally missed it.
Here is the corrected code
declare @max_amount DECIMAL(10,2) = 50
;with cte as
(
select sum(t.price) over (order by t.price asc, t.product asc, v.N asc) RunningTotal, *
from #TEMP1 t
cross apply dbo.fnTally(1, t.QtyAvailableToPurchase) v
where v.N * t.price <= @max_amount
)
select count(*)
from cte
where RunningTotal <= @max_amount -
Jonathan AC Roberts solved this problem very nicely using the fnTally() function to due the "Relational Multiplication of Rows". If we tweak his good code just a little bit, it will do the "pick" amongst equally priced items in from the largest quantity to the smallest. If the quantities have an "overlap" according to qty.N in the code below, it will try to "balance the inventory" so that you don't totally exhaust one product before moving on to the next.
It also produces the quantity consumed by product as well as the final quantity requested in the original post.
Here's the test table I used...
--===== Case 0 - 16 Items expected (I created this case)
DROP TABLE IF EXISTS #Temp0;
CREATE TABLE #Temp0
(
Product VARCHAR(20)
,QtyAvailableToPurchase INT
,Price DECIMAL(10,2)
)
;
INSERT INTO #Temp0
(Product,QtyAvailableToPurchase,Price)
VALUES ('Orange',5,3)
,('Cherry',10,3)
,('Apple', 6,3)
;... here's the code that relies heavily on Jonathan's "Relational Multiplication" method...
--=====================================================================================================================
--Answer by Jonathan A.C. Roberts with a "kicker" from me.
--It tries to balance the inventory using the descending sort order of qty.N to create "overlapping picks".
--https://www.sqlservercentral.com/forums/topic/find-maximum-qty-of-fruits-regardless-of-fruit-which-could-be-bought-by-50#post-4020716
--=====================================================================================================================
WITH cte AS
(--==== Calculates the running totals starting with the lowest priced items.
-- Unfortunately, it doesn''t have an early cutoff.
SELECT RunningTotal = SUM(t.price) OVER (ORDER BY t.price, qty.N DESC, Product) --I added "Product" and "DESC".
,*
FROM #Temp0 t
CROSS APPLY dbo.fnTally(1,t.QtyAvailableToPurchase) qty --Relational Multiplication
WHERE qty.N * price <= 50
)--==== Filter at the cutoff and display the individual Product quantities with a total.
SELECT Product = IIF(GROUPING(Product) = 0,Product,'Total')
,ProductCount = COUNT(*)
FROM cte
WHERE RunningTotal <= 50
GROUP BY Product WITH ROLLUP
ORDER BY GROUPING(Product),Product
;... and here are the results...
Again, nice job Jonathan!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jonathan AC Roberts wrote:
Thanks Jeff, well spotted, I totally missed it.
The first time I looked at it, SO DID I. 😀
Again, nice use of the "Relational Multiplication" method! I'm still thinking about how to do an "early out" to keep the additional rows from having to form after the goal has been reached.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - pietlinden wrote:
Maybe do the P*Q for the lowest priced fruits, sort that way, then do a running total. Filter for running total less than your threshold?
Spot on early suggestion, Piet!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply