Post reply

Find Maximum Qty of fruits (regardless of fruit) which could be bought by $50

  • April 23, 2022 at 4:10 pm

    #4021052

    You can do it with a cursor which can be faster if you a purchasing a large number of products:

    IF OBJECT_ID (N'tempdb..#TEMP1', N'U') IS NOT NULL DROP TABLE #TEMP1
    CREATE TABLE #TEMP1 
    (
        Product VARCHAR(20),
        QtyAvailableToPurchase BIGINT,
        Price DECIMAL(18,2)
    );

    INSERT INTO #TEMP1 (Product,QtyAvailableToPurchase,Price)
    VALUES ('Apple',150000,3),
           ('Orange',150000,5),
           ('Cherry',100000,10)

    :

    DECLARE @MaxAmount decimal(10, 2) = 500000

    DECLARE myCursor cursor FOR
        SELECT Product, QtyAvailableToPurchase, Price, QtyAvailableToPurchase*Price AS TotalProductPrice
          FROM #TEMP1 t
         WHERE Price < @MaxAmount
         ORDER BY Price, Product

    DECLARE @Product varchar(20),
            @QtyAvailableToPurchase bigint, 
            @Price decimal(18,2),
            @TotalProductPrice decimal(18,2)

    DECLARE @TotalItems int = 0, @RunningSum decimal(18,2) = 0

    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @Product, @QtyAvailableToPurchase, @Price, @TotalProductPrice 
    WHILE @@FETCH_STATUS = 0 BEGIN
        IF (@MaxAmount - @RunningSum) >= @TotalProductPrice BEGIN
            SET @TotalItems += @QtyAvailableToPurchase
            SET @RunningSum += @TotalProductPrice
            -- print concat('Product: ', @Product, ', Quantity: ', @QtyAvailableToPurchase)
        END ELSE IF (@MaxAmount - @RunningSum) >= @Price BEGIN
            -- print concat('Product: ', @Product, ', Quantity: ', FLOOR(@MaxAmount - @RunningSum + 0.0))
            SET @TotalItems += FLOOR((@MaxAmount - @RunningSum)/@Price) 
            SET @RunningSum += @Price*FLOOR((@MaxAmount - @RunningSum)/@Price)  
        END ELSE 
            BREAK -- exit while
        FETCH NEXT FROM myCursor INTO @Product, @QtyAvailableToPurchase, @Price, @TotalProductPrice  
    END
    CLOSE myCursor
    DEALLOCATE myCursor

    SELECT @TotalItems 'TotalItems'

  • May 27, 2022 at 1:47 pm

    #4040266

    Apologies, test post.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply