I've decided to come up with a slightly different approach. This one is set based and the general idea is to find all possible combinations of amounts of bags that match the required condition, then select the cheapest one.
Steps:
- given
@ReqQty, for each kind of Bag find how many of such Bag does it make sense to include in expression (thats is if bag contains 5 pieces, and we want to purchase 12 pieces, it makes sense to consider 1, 2 or 3 bags, but 4 bags would be obviously too much)
- find all possible combination of all Bags and their amounts (that is i.e. for Bag kind
A with amounts 1, 2 and 3, and Bag kind B with amounts 1 and 2 one can try: A * 1 + B * 1, A * 2 + B * 1, A * 3 + B * 1, A * 1 + B * 2, A * 2 + B * 2, A * 3 + B * 2)
- calculate all combinations (this actually done on the fly), that is find total quantity and total price
- get row with lowest price that is higher or equal to required quantity
This is whole solution with sample data OP provided:
(The solution was modified, new version is available below!)
-- sample data
declare @ReqQty int = 21
declare @Bags table (Code nvarchar(1), Quantity int, Price decimal(10,2))
insert into @Bags
select 'X', 16, 25.00
union
select 'Y', 8, 18.00
union
select 'Z', 4, 7
; with
-- helper table: all possible integer numbers <= @ReqQty
Nums (I) as
(
select 1
union all
select I + 1
from Nums
where I < @ReqQty
),
-- possible amounts of each kind bag that make sense
-- i.e. with 3-piece bag and 5-piece requirement it
-- is worth checking 1 (x3 = 3) or 2 (x2 = 6) bags, but
-- 3, 4... would be definitely too much
Vars (Code, Amount) as
(
select B.Code, Nums.I
from @Bags as B
inner join Nums on B.Quantity * I - @ReqQty < B.Quantity
),
Sums (Expr, Amount, TotalQuantity, TotalPrice) as
(
-- take each kind of bag with every amount as recursion root
select
convert(nvarchar(100), V.Code + '(' + convert(nvarchar(100), Amount) + ')'),
Amount,
B.Quantity * Amount,
convert(decimal(10, 2), B.Price * Amount)
from Vars as V
inner join @Bags as B on V.Code = B.Code
union all
-- add different kind of bag to the summary
-- 'Sums.Amount >= V.Amount' is to eliminate at least some duplicates
select
convert(nvarchar(100), Expr + ' + ' + V.Code + '(' + convert(nvarchar(100), V.Amount) + ')'),
V.Amount,
Sums.TotalQuantity + B.Quantity * V.Amount,
convert(decimal(10, 2), Sums.TotalPrice + B.Price * V.Amount)
from Vars as V
inner join @Bags as B on V.Code = B.Code
inner join Sums on (charindex(V.Code, Expr) = 0) and Sums.Amount >= V.Amount
)
-- now find lowest price that matches required quantity
-- remove 'top 1' to see all combinations
select top 1 Expr, TotalQuantity, TotalPrice from Sums
where TotalQuantity >= @ReqQty
order by TotalPrice asc
For a given sample data this is the result:
Expr TotalQuantity TotalPrice
Z(2) + X(1) 24 39.00
The solution is definitely not perfect:
- I do not like using
charindex to eliminate same types of bags
- all duplicate combinations should be eliminated
- I am not sure about efficiency
but I simply lacked time or skills to come up with more clever ideas. What I think is nice is that it's purely set-based, declarative solution.
EDIT
I've modified the solution a bit to get rid of charindex (and thus get rid of dependency of text based bag identifiers). Unfortunately I had to add 0 amount for each kind of bag which made even more combinations but it seems to have no noticeable impact on performance. Also for the same price the combination with more pieces is shown. :-)
-- sample data
declare @ReqQty int = 21
declare @Bags table (Code nvarchar(1), Quantity int, Price decimal(10,2))
insert into @Bags
select 'X', 16, 25.00
union
select 'Y', 8, 18.00
union
select 'Z', 4, 7.00
; with
-- helper table to apply order to bag types
Bags (Code, Quantity, Price, BI) as
(
select Code, Quantity, Price, ROW_NUMBER() over (order by Code)
from @Bags
),
-- helper table: all possible integer numbers <= @ReqQty
Nums (I) as
(
select 0
union all
select I + 1
from Nums
where I < @ReqQty
),
-- possible amounts of each kind bag that make sense
-- i.e. with 3-piece bag and 5-piece requirement it
-- is worth checking 1 (x3 = 3) or 2 (x2 = 6) bags, but
-- 3, 4... would be definitely too much
Vars (Code, Amount) as
(
select B.Code, Nums.I
from Bags as B
inner join Nums on B.Quantity * I - @ReqQty < B.Quantity
),
Sums (Expr, Amount, BI, TotalQuantity, TotalPrice) as
(
-- take first kind of bag with every amount as recursion root
select
convert(nvarchar(100), V.Code + '(' + convert(nvarchar(100), Amount) + ')'),
Amount, B.BI,
B.Quantity * Amount,
convert(decimal(10, 2), B.Price * Amount)
from Vars as V
inner join Bags as B on V.Code = B.Code
where B.BI = 1
union all
-- add different kind of bag to the summary
select
convert(nvarchar(100), Expr + ' + ' + V.Code + '(' + convert(nvarchar(100), V.Amount) + ')'),
V.Amount, B.BI,
Sums.TotalQuantity + B.Quantity * V.Amount,
convert(decimal(10, 2), Sums.TotalPrice + B.Price * V.Amount)
from Vars as V
inner join Bags as B on V.Code = B.Code
-- take next bag kind according to order
inner join Sums on B.BI = Sums.BI + 1
and Sums.TotalQuantity + B.Quantity * V.Amount - @ReqQty < B.Quantity
)
-- now find lowest price that matches required quantity
-- remove 'top 1' to see all combinations
select top 1 Expr, TotalQuantity, TotalPrice from Sums
where TotalQuantity >= @ReqQty
order by TotalPrice asc, TotalQuantity desc, Expr asc
