#StackBounty: #sql-server #t-sql SQL query for FIFO allocation of items

Bounty: 100

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 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


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.