Below we have two tables, one of purchase orders and the other of sales orders. What I'm trying to do is assign each sales order to a purchase order, with free stock. Which I can do with the following query:

Table 1 - Incoming Purchase Orders:

Table 2 - Sales Orders:

SELECT 
    a.number,
    a.item,
    a.createDate,
    a.qtyNeeded,
    (SELECT TOP 1 x.number FROM purchaseOrders x WHERE a.item = x.item ORDER BY x.createDate, x.number) as 'allocateToPoNum'
FROM salesOrder a
ORDER BY a.createDate

This returns the following:

The issue I'm having and cannot seem to think of a solution for is that query will only return the first purchase order in the list, but by the third line, all of the free qty of that purchase order is used up.

Purchase 12 has 5 freeQty on it. Sales orders 1234 & 2345 need a combined total of 5 qty. They should both have a allocateToPoNum = 12.

Sale orders 3456, 4567 & 4574 need 40 total qty, they can't be allocated to purchase order 12 as thats now all used up by the prior lines. So should have a allocateToPoNum = 22

What I want to happen is once all of the free qty of the selected purchase order is used up the query should then use the next purchase order with free stock so on. So the output of the query should look like this:

Any ideas on how to solve this issue would be massively appreciated. I've tried to be as detailed as possible, but I've missed anything please let me know.

Thank you.

CodePudding user response：

This seems to work with your data. I just added a column to each table to calculate the prior summations to make things easier:

declare @incomingPO table (
number int,
item varchar(20),
shipDate date,
qty int,
priorQty int)
insert into @incomingPO select 12, 'toy', '2021-11-20', 5,0
insert into @incomingPO select 22, 'toy', '2021-11-24', 40,0
insert into @incomingPO select 23, 'toy', '2021-11-27', 140,0
insert into @incomingPO select 34, 'toy', '2021-12-20', 400,0
update @incomingPO set priorQty = (select sum(qty) from @incomingPO s where s.number <= po.number) from @incomingPO po

select * from @incomingPO

    declare @salesOrders table (
    number int,
    item varchar(20),
    createDate date,
    qtyNeeded int,
    priorUsedQty int
    )
insert into @salesOrders select 1234, 'Toy', '2021-06-03', 3, 0
insert into @salesOrders select 2345, 'Toy', '2021-08-09', 2, 0
insert into @salesOrders select 3456, 'Toy', '2021-08-26', 30, 0
insert into @salesOrders select 4567, 'Toy', '2021-08-31', 6, 0
insert into @salesOrders select 4574, 'Toy', '2021-09-02', 4, 0
insert into @salesOrders select 5685, 'Toy', '2021-10-13', 100, 0

update @salesOrders SET priorUsedQty = (select sum(qtyNeeded) FROM @salesOrders s where s.number <= so.number) from @salesOrders so
select  
so.*,
(select top 1 number from @incomingPO po where so.priorUsedQty <= po.priorQty ) as [allocateToPoNum]
from @salesOrders so

Outputs a table with an increasing PO number assigned to the sales orders: