SQL Returning remaining rows

Question

I have a table of on hand quantity for a list of parts. I have a second table with a series of order ship dates and required quantities.

I want to return the ship date of the next order that we will not have anymore inventory for.

For example:

Table 1, our on hand quantity for parts A, B, and C:

Name   Qty-Have
A      10
B      10
C      5

Table 2, Next two months of future orders for parts A, B, and C:

Name   Due          Qty-Need
A      11/10/17     4
A      11/15/17     6
A      11/20/17     3
A      11/25/17     10
B      11/12/17     4
B      12/15/17     4
B      12/29/17     4
C      11/10/17     7

Result, the earliest order date for when we will have insufficient inventory:

Name   Next Due         Qty-Want         
A      11/20/17         13
B      12/29/17         2   
C      11/10/17         2   

I know I can do a grouping of Table 2 and subtract inventory to get Qty-Want, but I do not know how to get the next shipment date of the next uncovered order.

The first two orders of part A we have inventory, and I would like to know that we do not have enough for the third order, due 11/20/17 and we should make 13 parts to cover all remaining demand.

Answer 1

If you have window functions available (SQL Server or MySQL 8 and above) use the following query:

select h.name Name, min(o.due) Next_Due, max(o.sumQty) - h.Qtyhave Qty_Want
from
(
  select *, sum(QtyNeed) over (partition by name order by due) sumQty
  from orders
) o
right join onHand h on o.name = h.name and o.sumQty > h.Qtyhave
group by h.name, h.Qtyhave 

demo - works for SQL Server as well as for MySQL 8 and above

Result

Name    Next Due    Qty-Want
----------------------------------
A       11/20/17    13
B       12/29/17    2
C       11/10/17    2

Answer 2

you could use a sum window function to compute a running total by 'name', and observe where it goes negative:

select ord.name,
       ord.due,
       ord.qty,
       q.qty - ord.ord_sum inventory
from (  select *, 
                sum(qty) over (partition by name order by Due) ord_sum
        from fut_orders ) ord
inner join quantity q
    on ord.name = q.name

order by ord.name, ord.due