Maximum number of products for given amount from the product list in SQL server without using While/Cursor?

Question

I have a table named 'Product'

Create table products(ProductName VARCHAR(100), Price INT, Quantity INT)

Insert into products values ('Pencil',3,20)
Insert into products values ('Rubber',4,5)
Insert into products values ('Scale',15,4)

ProductName  Price       Quantity
------------------------ -----------
Pencil       3           20
Rubber       4           5
Scale        4           15

Here is the requirement.

What is the maximum number of items that can be bought by spending at most $100?

I just need the output value as 26. (20 Pencils + 5 Rubbers + 1 Scale)

I don't want the list of items, I just need the maximum possible products.

If the table only have the below values

ProductName  Price       Quantity
------------ ----------- -----------
Book         90          2
Note         120         4

I just need to return 1(Can't buy any more products than 1 book)

This needs to achieve in T-SQL. We are not allowed to use WHILE or CURSOR.

Answer 1

You can use a recursive common table expression (CTE) to create every possible case, a query would be like that:

DECLARE @Query NVARCHAR(max) = ';WITH cte as (
    SELECT ProductName,Price,1 AS Qte
    FROM #products
    UNION ALL
    SELECT cte.ProductName,cte.Price,Qte + 1
    FROM cte
    JOIN #products p ON p.ProductName = cte.ProductName
    WHERE cte.Qte < p.Quantity
)
SELECT MAX(c1.Price * c1.Qte + c2.Price * c2.Qte + c3.Price * c3.Qte), MAX(c1.Qte + c2.Qte + c3.Qte) 
FROM cte c1'

;with cte as 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ProductName) AS NB_CONDITION, 1 AS increment
    FROM #products

    UNION ALL

    SELECT p.*,c.NB_CONDITION,increment + 1
    FROM cte c
    JOIN #products p ON c.ProductName = p.ProductName 
    WHERE c.increment < NB_CONDITION
)

SELECT @Query = @Query + CONCAT(' INNER JOIN cte c', NB_CONDITION, ' ON ', STRING_AGG(CONCAT('c' ,NB_CONDITION ,'.ProductName <> c', increment , '.ProductName'), ' AND '))
FROM cte
WHERE NB_CONDITION <> increment
GROUP BY NB_CONDITION

SET @Query = @Query + ' WHERE c1.Price * c1.Qte + c2.Price * c2.Qte + c3.Price * c3.Qte <= 100'

PRINT (@Query)

exec (@Query)

I used dynamic SQL to create the joints to ensure different combinations.