# Allocation of resources query

I have two tables, one for resources and another for the required allocation.

For instance:

Resource Count
A 3
B 2
C 8
Required Count
X 4
Y 3
Z 5

The resources table tells how many things I've got from each resource and the required table tells how many resources I need to allocate this item.

In the above example:

• For X we need to assign 3 times A and 1 time B.
• For Y we need to assign 1 time B and 2 time C.
• For Z we need to assign 5 times C.

We will have some spare C items…

Currently, I've tried to add a calculated column for both tables (Running total - https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server):

Resource Count Running_total
A 3 3
B 2 5
C 8 13
Required Count Running_total
X 4 4
Y 3 7
Z 5 12

And have a cartesian multiplication of the tables but that's my dead-end, My main issue is how to have a formula that will know to split one resource into multiple assignments or one assignment from multiple resources.

Can someone suggest a clue?

EDIT

Schema:

``````CREATE TABLE Resources (
ResID int primary key,
ResName varchar(255),
ResCnt int
);

CREATE TABLE Required (
ReqID int primary key,
ReqName varchar(255),
ReqCnt int
);

insert into Resources values(1, 'A', 3);
insert into Resources values(2, 'B', 2);
insert into Resources values(3, 'C', 8);

insert into Required values(101, 'X', 4);
insert into Required values(102, 'Y', 3);
insert into Required values(103, 'Z', 5);
``````

Queries:

`````` SELECT ResName, ResCnt,
SUM(ResCnt) OVER(ORDER BY ResName
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Resources;

SELECT ReqName, ReqCnt,
SUM(ReqCnt) OVER(ORDER BY ReqName
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Required;
``````
• "For Y we need to assign 1 time B and 1 time C." 1 + 1 = 2 but your `Count` for Y is 3. Also it is not clear how you determine which `Resource` you need to allocate to each `Required` item, i.e. how do you know for Z you need 5 C's, and not 4 C's and 1 B instead? Please provide the query you've written so far and update your post with the answers to my questions.
– J.D.
Dec 1, 2021 at 23:19
• @J.D. Great catch on the Y count, thanks! all the resources are equivalent but coming from a different bucket and for the task, we need to know where they came from and where they go to. I've added a sqlfiddle Dec 1, 2021 at 23:52
• I tried to have a cartesian multiplication of them with another column that says how much from a resource to a required ( with many zeros, i.e. C will have 0 on A&B) but this way isn't mandatory, actually, other representation will also be good Dec 2, 2021 at 0:39

One way to approach this problem is by defining intervals, cross joining together the two tables, keeping the rows with overlapping intervals, and calculating the overlap between each interval. The query below does that without paying much mind to performance:

``````WITH Resources_CTE AS (
SELECT
ResID,
ResName,
ResCnt,
RT - ResCnt IntervalStart,
RT IntervalEnd
FROM
(
SELECT *, SUM(ResCnt) OVER(ORDER BY ResName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RT
FROM Resources
) q
),
Required_CTE AS (
SELECT
ReqID,
ReqName,
ReqCnt,
RT - ReqCnt IntervalStart,
RT IntervalEnd
FROM
(
SELECT *, SUM(ReqCnt) OVER(ORDER BY ReqName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RT
FROM [Required]
) q
)
SELECT req.ReqID, req.ReqName, res.ResID, res.ResName,
CASE WHEN req.IntervalEnd - res.IntervalStart < res.IntervalEnd - req.IntervalStart THEN req.IntervalEnd - res.IntervalStart ELSE res.IntervalEnd - req.IntervalStart END AssignmentCount
FROM Required_CTE req
CROSS JOIN Resources_CTE res
WHERE req.IntervalEnd >= res.IntervalStart AND req.IntervalStart <= res.IntervalEnd
ORDER BY req.ReqID, res.ResID;
``````

I matched the way that you calculated the running totals in the question, but it would be more typical to order by the primary key columns instead of by the name columns.

Here is the result set for your sample data:

• Thanks for your quick response Joe Dec 2, 2021 at 7:53