Hi, following is the test data:
DECLARE @test TABLE(
[GroupNumber] [int] NOT NULL,
[ProductID] [nvarchar](50) NOT NULL PRIMARY KEY,
[FloorNumber] [int] NOT NULL,
[ProductCount] [int] NOT NULL
)
INSERT INTO @test( GroupNumber,ProductID,FloorNumber, ProductCount)
SELECT 1,'100',2,2
UNION
SELECT 1,'200',2,2
UNION
SELECT 2,'300',4,3
UNION
SELECT 2,'400',4,3
UNION
SELECT 2,'450',4,3
UNION
SELECT 3,'500',7,3
UNION
SELECT 3,'600',7,3
UNION
SELECT 3,'700',7,3
UNION
SELECT 4,'550',1,3
UNION
SELECT 4,'650',1,3
UNION
SELECT 4,'750',1,3
UNION
SELECT 5,'150',1,1
UNION
SELECT 6,'250',1,1
UNION
SELECT 7,'350',1,1
SELECT * FROM @test
ORDER BY GroupNumber
DECLARE @RequestedQuantity INT = 2
EXPECTED OUTPUT: Given A requested quantity -
1. IF @RequestedQuantity = 2
GroupNumber = 5,6 OR 5,7 OR 6,7 (ALL ARE VALID, CAN PICK ANY)
2. IF @RequestedQuantity = 3
GroupNumber = 5,6,7 (this IS the best combination AS ALL 3 are spread across different floors)
3. IF @RequestedQuantity = 4
GroupNumber = 1,5,6 OR 1,6,7 OR 1,5,7(IN this CASE pick ANY two GROUPs WITH a COUNT OF 1 AND GROUP WITH a 2; these ARE the best combinations & spread across floors)
4. IF @RequestedQuantity = 6
GroupNumber = 2,5,6,7 OR 3,5,6,7, OR 4,5,6,7
Rules:
1. Pick complete GROUP i.e. IF one product IS picked THEN ALL products from that GROUP have to be picked thus we should COUNT ALL products FROM that GROUP
2. Spread across floors: IF possible, Pick the groups which are spread across floors to fulfill the requested quantity
3. if requested quantity IS fulfilled, then pick the groups which make up the least no. of extra products
Ex: 3 groups across 3 levels have 4,4,3 products and request is for 6 products then we pick groups 4,3 instead of 4,4
So to sum it up:
pick the least amount of products that fulfill the request (can be an exact match or over the requested quantity but find the ones which are spread across floors)
I have tried cube, to find all possible combinations of the GroupNumber, it works but only till 12 groups as cube has this limitation and if we exceed 12 say to 30 or 40 groups then the possible combinations can run into millions. I am wondering if there is any other way to solve this.
Thanks