1

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

SqlFiddle link:http://sqlfiddle.com/#!18/de1417/3

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;
3
  • "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
  • 1
    @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
    – yossico
    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
    – yossico
    Dec 2, 2021 at 0:39

1 Answer 1

1

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:

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.