I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.
We have Buyers and Items.
DECLARE @Buyer TABLE
(
BuyerId INT IDENTITY(1,1),
Name NVARCHAR(100),
Budget INT
);
INSERT @Buyer (Name, Budget) VALUES
('Anna', 100),
('Brett', 50),
('Conor', 20)
DECLARE @Item TABLE
(
ItemId INT IDENTITY(1,1),
Cost INT
);
INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);
The goal is to craft a query (no loops or cursor) which allocates items to buyers, based on their budget. The most expensive items should be allocated in priority.
Expected result:
ItemId BuyerName
1 Anna
4 Anna
6 Brett
7 NULL
2 NULL
3 Conor
5 Anna
8 Brett
9, NULL
From a logical perpective, this is what happens. First, the items are sorted by descending cost
ItemId Cost
1 50
4 40
6 40
7 30
2 30
3 20
5 10
8 10
9 5
Then we go through each item and try to allocate it to a buyer that has enough budget left.
- Item 1 => Can be assigned to Anna. Anna's remaining budget is 100 - 50 = 50
- Item 4 => Can be assigned to Anna. Anna's remaining budget is 50 - 40 = 10
- Item 6 => Can be assigned to Brett. Brett's remaining budget is 50 - 40 = 10
- Item 7 => No one has enough budget left
- Item 2 => No one has enough budget left
- Item 3 => Can be assigned to Conor. Conor's remaining budget is 20 - 20 = 0
- Item 5 => Can be assigned to Anna. Anna's remaining budget is 10 - 10 = 0
- Item 8 => Can be assigned to Brett. Brett's remaining budget is 10 - 10 = 0
- Item 9 => No one has enough budget left