How do I join 2 tables to allocate items?

Question

I've created 2 tables that have inventory information (item, location, qty). One of them NeedInv has item/location(s) that need X number of items. The other HaveInv has item/locations(s) with excess X number of items.

I'm trying to join or combine the 2 tables to output which items should be transferred between which locations. I have code that does this for a single distribution location & I've attempted to modify it and add logic to have it work with multiple distribution locations, but it still fails in certain situations.

I've created a [sqlfiddle]1, but the sample data is like so:

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)

I was trying to output something like this as a result of joining the tables:

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

My current query to join the 2 tables looks like so:

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

It seems to work for most cases except when Allocated is set to zero, but there's still items that could be transferred. This can be seen for item 200 1 where location B only needs 1 but is going to receive 2 items, while location C which also needs 1 item will receive 0.

Any help/guidance would be appreciated!

Answer 1

Your query looks a little complicated for what it needs to do, IMO.

As far as I can tell, this is just a simple matter of building the logic into a query using running totals of inventory. Essentially, it's just a matter of building in rules such that if what you need can be taken from a source location, you take it, otherwise you take as much as possible.

For example, I believe the following query contains the logic required:

SELECT N.Item,
       SourceLocation = H.Location,
       TargetLocation = N.Location,
       Qty = 
        CASE 
            WHEN N.TotalRunningRequirement <= H.TotalRunningInventory -- If the current source location has enough stock to fill the request.
            THEN 
                CASE
                    WHEN N.TotalRunningRequirement - N.Need < H.TotalRunningInventory - H.Have -- If stock required has already been allocated from elsewhere.
                    THEN N.TotalRunningRequirement - (H.TotalRunningInventory - H.Have) -- Get the total running requirement minus stock allocated from elsewhere.
                    ELSE N.Need -- Otherwise just take how much is needed.
                END
            ELSE N.Need - (N.TotalRunningRequirement - H.TotalRunningInventory) -- Current source doesn't have enough stock to fulfil need, so take as much as possible.
        END
FROM
( 
    SELECT *, TotalRunningRequirement = SUM(need) OVER (PARTITION BY item ORDER BY location)
    FROM NeedInv
    WHERE need > 0
) AS N
JOIN 
(
    SELECT *, TotalRunningInventory = SUM(have) OVER (PARTITION BY item ORDER BY location)
    FROM HaveInv
    WHERE have > 0  
) AS H
    ON H.Item = N.Item
    AND H.TotalRunningInventory - (N.TotalRunningRequirement - N.need) > 0 -- Join if stock in source location can be taken
    AND H.TotalRunningInventory - H.Have - (N.TotalRunningRequirement - N.need) < N.TotalRunningRequirement
;

Note: Your desired output doesn't seem to match your sample data for Item 200 as far as I can tell.

Answer 2

I was wondering if a Recursive CTE could be used for allocations.

But it turned out a bit more complicated.

The result doesn't completely align with those expected result in the question.
But since the other answer returns the same results, I guess that's fine.

So see it as just an extra method.

Test on db<>fiddle here

It basically loops through the haves and needs in the order of the calculated row_numbers.
And assigns what's still available for what's still needed.

declare @HaveNeedInv table (
 item int,
 rn int,
 loc varchar(1),
 have int,
 need int,
 primary key (item, rn, loc, have, need)
);

insert into @HaveNeedInv (item, loc, have, need, rn)
select item, location, sum(have), 0 as need,
row_number() over (partition by item order by sum(have) desc)
from HaveInv
where have > 0
group by item, location;

insert into @HaveNeedInv (item, loc, have, need, rn)
select item, location, 0 as have, sum(need),
row_number() over (partition by item order by sum(need) desc)
from NeedInv
where need > 0
group by item, location;

;with ASSIGN as
(
    select h.item, 0 as lvl, 
     h.rn as hrn, n.rn as nrn,
     h.loc as hloc, n.loc as nloc, 
     h.have, n.need, 
     iif(h.have<=n.need,h.have,n.need) as assign 
    from @HaveNeedInv h
    join @HaveNeedInv n on (n.item = h.item and n.need > 0 and n.rn = 1)
    where h.have > 0 and h.rn = 1

    union all

    select t.item, a.lvl + 1,
     iif(t.have>0,t.rn,a.hrn),
     iif(t.need>0,t.rn,a.nrn),
     iif(t.have>0,t.loc,a.hloc),
     iif(t.need>0,t.loc,a.nloc),
     iif(a.have>a.assign,a.have-a.assign,t.have),
     iif(a.need>a.assign,a.need-a.assign,t.need),
     case 
     when t.have > 0
     then case 
          when t.have > (a.need - a.assign) then a.need - a.assign
          else t.have
          end
      else case 
           when t.need > (a.have - a.assign) then a.have - a.assign
           else t.need
           end
      end
    from ASSIGN a
    join @HaveNeedInv t 
      on t.item = a.item
    and iif(a.have>a.assign,t.need,t.have) > 0
    and t.rn = iif(a.have>a.assign,a.nrn,a.hrn) + 1
)
select 
item,
hloc as SourceLocation,
nloc as TargetLocation,
assign as Qty
from ASSIGN
where assign > 0
order by item, hloc, nloc
option (maxrecursion 1000);

Result:

100  B  A  3
100  D  A  1
100  D  C  2
200  A  B  1
200  D  C  1
300  C  A  3
300  C  B  5

Changing the order in the row_numbers (to fill @NeedHaveInv) will change the priority, and could return a different result.