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

``````DECLARE @Buyer TABLE
(
Name NVARCHAR(100),
Budget INT
);

('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!!!

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