Decreasing Cumulative Sum That When Equal to or Less Than Zero Change Another Value

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:

number	item	shipDate	qty	usedQty	freeQty
12	Toy	2021-11-20	100	95	5
22	Toy	2021-11-24	230	190	40
23	Toy	2021-11-27	145	140	140
34	Toy	2021-12-20	400	400	400

Table 2 – Sales Orders:

number	item	createDate	qtyNeeded	allocateToPoNum
1234	Toy	2021-06-03	3
2345	Toy	2021-08-09	2
3456	Toy	2021-08-26	30
4567	Toy	2021-08-31	6
4574	Toy	2021-09-02	4
5685	Toy	2021-10-13	100

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:

number	item	createDate	qtyNeeded	allocateToPoNum
1234	Toy	2021-06-03	3	12
2345	Toy	2021-08-09	2	12
3456	Toy	2021-08-26	30	12
4567	Toy	2021-08-31	6	12
4574	Toy	2021-09-02	4	12
5685	Toy	2021-10-13	100	12

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:

number	item	createDate	qtyNeeded	allocateToPoNum
1234	Toy	2021-06-03	3	12
2345	Toy	2021-08-09	2	12
3456	Toy	2021-08-26	30	22
4567	Toy	2021-08-31	6	22
4574	Toy	2021-09-02	4	22
5685	Toy	2021-10-13	100	23

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.

Answer

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: