SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE NeedInv
    (item int, location varchar(1), need int)
    
INSERT INTO NeedInv
    (item, location, need)
VALUES
    (100, 'A', 4), (100, 'B', 0), (100, 'C', 2), (200, 'A', 0), (200, 'B', 1), (200, 'C', 1), (300, 'A', 3), (300, 'B', 5), (300, 'C', 0)

CREATE TABLE HaveInv
    (item int, location varchar(1), have int)
    
INSERT INTO HaveInv
    (item, location, have)
VALUES
    (100, 'A', 0), (100, 'B', 3), (100, 'C', 0), (100, 'D', 3), (200, 'A', 1), (200, 'B', 0), (200, 'C', 0), (200, 'D', 1), (300, 'A', 0), (300, 'B', 0), (300, 'C', 20), (300, 'D', 5)

CREATE TABLE DesiredOutput
    (item int, SourceLocation varchar(1), TargetLocation varchar(1), Qty int)
    
INSERT INTO DesiredOutput
    (item, SourceLocation, TargetLocation, Qty)
VALUES
    (100, 'B', 'A', 3), (100, 'D', 'A', 1), (100, 'D', 'C', 2), (200, 'A', 'B', 2), (200, 'A', 'C', 3), (200, 'D', 'C', 1), (300, 'C', 'A', 3), (300, 'C', 'B', 3)
29 rows affected

select * from NeedInv

select * from HaveInv


item
location
need


100
A
4

100
B
0

100
C
2

200
A
0

200
B
1

200
C
1

300
A
3

300
B
5

300
C
0




item
location
have


100
A
0

100
B
3

100
C
0

100
D
3

200
A
1

200
B
0

200
C
0

200
D
1

300
A
0

300
B
0

300
C
20

300
D
5


select * from DesiredOutput


item
SourceLocation
TargetLocation
Qty


100
B
A
3

100
D
A
1

100
D
C
2

200
A
B
2

200
A
C
3

200
D
C
1

300
C
A
3

300
C
B
3


select 
    n.*,
    (case when Ord <= Remainder and (RemaingNeed > 0 and RemaingNeed < RemainingInv) then Allocated + RemaingNeed else case when RemaingNeed < 0 then 0 else Allocated end end) as NeedToFill
from (
select
    n.*,
    row_number() over(partition by item order by RN, (case when need > Allocated then 0 else 1 end)) as Ord,
    n.TotalAvail - sum(n.Allocated) over (partition by item) as Remainder
from (
select 
    n.*,
    n.TotalAvail - sum(n.Allocated) over (partition by item order by RN) as RemainingInv,
    n.need - sum(n.Allocated) over (partition by item, location order by RN) as RemaingNeed
from (
  select
    n.*,
    case when Proportional > need then need else Proportional end as Allocated
  from (
    select
      row_number() over(order by need desc) as RN,
      n.*,
      h.location as Source,
      h.have,
      h.TotalAvail,
      convert(int, floor(h.have * n.need * 1.0 / n.TotalNeed), 0) as Proportional
    from (
      select n.*, sum(need) over (partition by item) as TotalNeed
      from NeedInv n) n
    join (select h.*, sum(have) over (partition by item) as TotalAvail from HaveInv h) h
      on n.item = h.item
      and h.have > 0
    ) n
  ) n
) n
) n
where n.need > 0


RN
item
location
need
TotalNeed
Source
have
TotalAvail
Proportional
Allocated
RemainingInv
RemaingNeed
Ord
Remainder
NeedToFill


3
100
A
4
6
B
3
6
2
2
4
2
1
0
2

4
100
A
4
6
D
3
6
2
2
2
0
2
0
2

7
100
C
2
6
B
3
6
1
1
1
1
3
0
1

8
100
C
2
6
D
3
6
1
1
0
0
4
0
1

9
200
B
1
2
A
1
2
0
0
2
1
1
2
1

10
200
B
1
2
D
1
2
0
0
2
1
2
2
1

11
200
C
1
2
A
1
2
0
0
2
1
3
2
0

12
200
C
1
2
D
1
2
0
0
2
1
4
2
0

1
300
B
5
8
C
20
25
12
5
20
0
1
13
5

2
300
B
5
8
D
5
25
3
3
17
-3
2
13
0

5
300
A
3
8
C
20
25
7
3
14
0
3
13
3

6
300
A
3
8
D
5
25
1
1
13
-1
4
13
0

item	location	have
100	A	0
100	B	3
100	C	0
100	D	3
200	A	1
200	B	0
200	C	0
200	D	1
300	A	0
300	B	0
300	C	20
300	D	5

RN	item	location	need	TotalNeed	Source	have	TotalAvail	Proportional	Allocated	RemainingInv	RemaingNeed	Ord	Remainder	NeedToFill
3	100	A	4	6	B	3	6	2	2	4	2	1	0	2
4	100	A	4	6	D	3	6	2	2	2	0	2	0	2
7	100	C	2	6	B	3	6	1	1	1	1	3	0	1
8	100	C	2	6	D	3	6	1	1	0	0	4	0	1
9	200	B	1	2	A	1	2	0	0	2	1	1	2	1
10	200	B	1	2	D	1	2	0	0	2	1	2	2	1
11	200	C	1	2	A	1	2	0	0	2	1	3	2	0
12	200	C	1	2	D	1	2	0	0	2	1	4	2	0
1	300	B	5	8	C	20	25	12	5	20	0	1	13	5
2	300	B	5	8	D	5	25	3	3	17	-3	2	13	0
5	300	A	3	8	C	20	25	7	3	14	0	3	13	3
6	300	A	3	8	D	5	25	1	1	13	-1	4	13	0

item	location	have
100	A	0
100	B	3
100	C	0
100	D	3
200	A	1
200	B	0
200	C	0
200	D	1
300	A	0
300	B	0
300	C	20
300	D	5

RN	item	location	need	TotalNeed	Source	have	TotalAvail	Proportional	Allocated	RemainingInv	RemaingNeed	Ord	Remainder	NeedToFill
3	100	A	4	6	B	3	6	2	2	4	2	1	0	2
4	100	A	4	6	D	3	6	2	2	2	0	2	0	2
7	100	C	2	6	B	3	6	1	1	1	1	3	0	1
8	100	C	2	6	D	3	6	1	1	0	0	4	0	1
9	200	B	1	2	A	1	2	0	0	2	1	1	2	1
10	200	B	1	2	D	1	2	0	0	2	1	2	2	1
11	200	C	1	2	A	1	2	0	0	2	1	3	2	0
12	200	C	1	2	D	1	2	0	0	2	1	4	2	0
1	300	B	5	8	C	20	25	12	5	20	0	1	13	5
2	300	B	5	8	D	5	25	3	3	17	-3	2	13	0
5	300	A	3	8	C	20	25	7	3	14	0	3	13	3
6	300	A	3	8	D	5	25	1	1	13	-1	4	13	0

item	location	have
100	A	0
100	B	3
100	C	0
100	D	3
200	A	1
200	B	0
200	C	0
200	D	1
300	A	0
300	B	0
300	C	20
300	D	5

RN	item	location	need	TotalNeed	Source	have	TotalAvail	Proportional	Allocated	RemainingInv	RemaingNeed	Ord	Remainder	NeedToFill
3	100	A	4	6	B	3	6	2	2	4	2	1	0	2
4	100	A	4	6	D	3	6	2	2	2	0	2	0	2
7	100	C	2	6	B	3	6	1	1	1	1	3	0	1
8	100	C	2	6	D	3	6	1	1	0	0	4	0	1
9	200	B	1	2	A	1	2	0	0	2	1	1	2	1
10	200	B	1	2	D	1	2	0	0	2	1	2	2	1
11	200	C	1	2	A	1	2	0	0	2	1	3	2	0
12	200	C	1	2	D	1	2	0	0	2	1	4	2	0
1	300	B	5	8	C	20	25	12	5	20	0	1	13	5
2	300	B	5	8	D	5	25	3	3	17	-3	2	13	0
5	300	A	3	8	C	20	25	7	3	14	0	3	13	3
6	300	A	3	8	D	5	25	1	1	13	-1	4	13	0