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

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',4,15)

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.

CodePudding user response：

Perhaps a Running total can help here:

declare @products table (ProductName VARCHAR(100), Price INT, Quantity INT)

Insert into @products values ('Pencil',5,20)
Insert into @products values ('Rubber',30,5)
Insert into @products values ('Scale', 80,15)
Insert into @products values ('Book', 10,15)

select SUM(Quantity)
from
(
    select 
        *,
        SUM (Price) OVER (order by price rows unbounded preceding) AS RunningTotal
    from @products
) tbl
where RunningTotal <= 100

CodePudding user response：

You can use a recursive common table expression (CTE) to create every possible case, a qeury 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 insure different combinations.