Purchase order number to fulfil customer order number

order	Qty	Inventory	Type
1001	-1	-1	Customer Order
1002	-1	-2	Customer Order
1003	-1	-3	Customer Order
1004	-1	-4	Customer Order
1005	-5	-9	Customer Order
1006	3	-6	Purchase Order
1007	6	0	Purchase Order

With the above table, can we find the purchase order number that would fulfil customer order.
Example:

order #	Qty	Inventory	Type	Purchase order # that will fulfil order
1001	-1	-1	Customer Order	1006
1002	-1	-2	Customer Order	1006
1003	-1	-3	Customer Order	1006
1004	-1	-4	Customer Order	1007
1005	-5	-9	Customer Order	1007
1006	3	-6	Purchase Order
1007	6	0	Purchase Order

SQL

SELECT *
        , CASE WHEN Inventory >= 0 THEN OrderNumber
               ELSE LEAD(CASE WHEN QTY > 0 THEN OrderNumber END) IGNORE NULLS
                    OVER(ORDER BY OrderNumber)
          END PurchaseOrderNoToFulfil
FROM OrderTable;

The above query does not keep track of the purchase order that have already been used. Example, it shows purchase order # 1006 for both customer order # 1004 and 1005. Since only 3 Qty were available for 1006, it satisfies upto order 1003, but not beyond that.

For more context:
On order 1001, the inventory is -1 (oversold). Order 1002, the inventory is -2 and so on. On order 1006, there is an incoming purchase order of Qty 3 to fulfil the previous customer orders. But the quantity of 3 can only fulfil upto order 1003. Order 1004 and 1005 will have to wait until 1007 PO comes in.

CodePudding user response：

You can use a recursive CTE:

with recursive cte(id, r_id, s) as (
    select min(o.o), o1.o, o.qty   o1.qty from orders o 
    join orders o1 on o1.qty   o.qty >= 0 where o.qty < 0 and o1.qty = (select min(o2.qty) from orders o2 where o2.qty > 0)
    union all
    select c.id   1, case when c.s   o1.qty >= 0 or o3.o is null then c.r_id else o3.o end, 
        case when c.s   o1.qty >= 0 then c.s   o1.qty else c.s   o1.qty   o3.qty end 
    from cte c join orders o1 on c.id   1 = o1.o 
    left join orders o3 on o3.o > c.r_id where c.id   1 <= (select max(o2.o) from orders o2 where o2.qty < 0)
)
select o.*, c.r_id from orders o left join cte c on o.o = c.id

CodePudding user response：

I think this is giving me results in line with what you're asking for. Perhaps you'll find it useful.

with C as (
    select ordernum, -inventory as inventory,
        sum(-qty) over (order by ordernum) as fulfilled
    from O where "Type" = 'Customer Order'
), P as (
    select *, sum(qty) over (order by ordernum) as POed
    from O where "Type" = 'Purchase Order')
select ordernum, inventory, PO, POed
from C outer apply (
    select min(ordernum) as PO, min(POed) as POed from P where POed >= fulfilled
) oa

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=25a4efd31efc0827940a83b8658eb2b2