How to convert products to product-with-pack-sizes using the largest pack size

Question

For a food online-ordering application, I have worked out how many ingridients we need (which we call StockItems), but need help converting that to what we should order based on what sizes they come in (which we call SupplierItems -- i.e. StockItems + PackSizes).

If we take apples as an example, we need to order 46 (that bit has already been worked out). But apples only come in boxes of 20 and boxes of 5. You want order apples in the biggest boxes if possible and then over-order if you can't order the exact amount. If you need 46 apples, you'll order 2 boxes of 20 and 2 boxes of 5, which gives you 50 apples -- the best you're going to get with those pack sizes.

The SQL below creates all the tables needed and fills them will data. The StockItemsRequired table contains the 46 apples the we need. I have filled the SupplierItemsRequired table with the 2 boxes of 20 and 2 boxes of 5, but that is the part that I need to work out from the other tables.

My question is: what is the SQL to fill the SupplierItemsRequired table with the correct SupplierItems that need to be ordered -- based on the rules above. No solutions with cursors or loops, please -- I'm looking for a set-based solution (I'm sure it can be done!).

I'm using SQL Server 2008.

-- create tables
create table StockItems (StockItemID int primary key identity(1,1), StockItemName varchar(50))
create table PackSizes (PackSizeID int primary key identity(1,1), PackSizeName varchar(50))
create table SupplierItems (SupplierItemID int primary key identity(1,1), StockItemID int, PackSizeID int)
create table StockItemsRequired(StockItemID int, Quantity int)
create table SupplierItemsRequired(SupplierItemID int, Quantity int)

-- fill tables
insert into StockItems (StockItemName) values ('Apples')
insert into StockItems (StockItemName) values ('Pears')
insert into StockItems (StockItemName) values ('Bananas')
insert into PackSizes (PackSizeName) values ('Each')
insert into PackSizes (PackSizeName) values ('Box of 20')
insert into PackSizes (PackSizeName) values ('Box of 5')
insert into SupplierItems (StockItemID, PackSizeID) values (1, 2)
insert into SupplierItems (StockItemID, PackSizeID) values (1, 3)
insert into StockItemsRequired (StockItemID, Quantity) values (1, 46)
insert into SupplierItemsRequired (SupplierItemID, Quantity) values (1, 2)
insert into SupplierItemsRequired (SupplierItemID, Quantity) values (2, 2)


-- SELECT definition data
select * from StockItems -- ingredients
select * from PackSizes -- sizes they come in 
select si.SupplierItemID, st.StockItemName, p.PackSizeName -- how you buy these items
from SupplierItems si
inner join StockItems st on si.StockItemID = st.StockItemID
inner join PackSizes p on si.PackSizeID = p.PackSizeID

-- SELECT how many of the ingridients you need (StockItemsRequired)
select st.StockItemID, st.StockItemName, r.Quantity
from StockItemsRequired r
inner join StockItems st on r.StockItemID = st.StockItemID

-- SELECT how you need to buy these items (SupplierItemsRequired)
select si.SupplierItemID, st.StockItemName, p.PackSizeName, r.Quantity
from SupplierItemsRequired r
inner join SupplierItems si on r.SupplierItemID = si.SupplierItemID
inner join StockItems st on si.StockItemID = st.StockItemID
inner join PackSizes p on si.PackSizeID = p.PackSizeID

Answer 1

Blatantly disregarding the no loop requirement here's Peso's algorithm tweaked to fit this task. Be interested to see how this compares if any one takes up the set based challenge.

DECLARE @WantedValue INT;

SET @WantedValue = 101;

DECLARE @packsizes TABLE
(
size INT
)

INSERT INTO @packsizes 
SELECT 40 UNION ALL
SELECT 30 UNION ALL
SELECT 27 UNION ALL
SELECT 15 

-- Stage the source data
DECLARE @Data TABLE
    (
        RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
        MaxItems INT,
        CurrentItems INT DEFAULT 0,
        FaceValue INT,
        BestOver INT DEFAULT 1
    );

-- Aggregate the source data
INSERT      @Data
        (
            MaxItems,
            FaceValue
        )
SELECT      CEILING(@WantedValue/size),
        size
FROM        @packsizes
order by size desc


-- Declare some control variables
DECLARE @CurrentSum INT,
    @BestOver INT,
    @RecID INT

-- Delete all unworkable FaceValues 
DELETE
FROM    @Data
WHERE   FaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)


-- Update BestOver to a proper value
UPDATE  @Data
SET BestOver = MaxItems

-- Initialize the control mechanism
SELECT  @RecID = MIN(RecID),
    @BestOver = SUM(BestOver * FaceValue)
FROM    @Data

-- Do the loop!
WHILE @RecID IS NOT NULL
    BEGIN
        -- Reset all "bits" not incremented
        UPDATE  @Data
        SET CurrentItems = 0
        WHERE   RecID < @RecID

        -- Increment the current "bit"
        UPDATE  @Data
        SET CurrentItems = CurrentItems + 1
        WHERE   RecID = @RecID

        -- Get the current sum
        SELECT  @CurrentSum = SUM(CurrentItems * FaceValue)
        FROM    @Data
        WHERE   CurrentItems > 0

        -- Stop here if the current sum is equal to the sum we want
        IF @CurrentSum = @WantedValue
            BREAK
        ELSE
            -- Update the current BestOver if previous BestOver is more
            IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
                BEGIN
                    UPDATE  @Data
                    SET BestOver = CurrentItems

                    SET @BestOver = @CurrentSum
                END

        -- Find the next proper "bit" to increment
        SELECT  @RecID = MIN(RecID)
        FROM    @Data
        WHERE   CurrentItems < MaxItems
    END

-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
        SELECT  BestOver AS Items,
            FaceValue,
            SUM(BestOver*FaceValue) AS SubTotal
        FROM    @Data
        WHERE   BestOver > 0
        GROUP BY ROLLUP ((BestOver, FaceValue))
ELSE
    -- We have an exact match
    SELECT  CurrentItems AS Items,
        FaceValue,
        SUM(CurrentItems*FaceValue) AS SubTotal
    FROM    @Data
    WHERE   CurrentItems > 0
    GROUP BY ROLLUP ((CurrentItems, FaceValue))