SQL Returning remaining rows

user_5040920 0
Viewed 92

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.

sql-server mysql sql
3 Answers

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

user_15188670

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
user_20912470

This approach involves creating a view in MySQL. The view contains a rolling cumulative sum of the future inventory. The current Qty-have inventory is subtracted from the sum. Some rows will therefore have a negative value, and they are eliminated with the HAVING clause:

CREATE OR REPLACE VIEW future_orders AS
(SELECT 
  t2.name, 
  t2.due, 
  t1.qty_have as qty_have, 
  t2.qty_need, 
  (SELECT SUM(fo.qty_need) FROM table_2 fo WHERE fo.name = t2.name AND fo.due <= t2.due)-qty_have as qty_want
FROM table_2 t2
LEFT JOIN table_1 t1 ON t1.name = t2.name
HAVING qty_want > 0);

Once the view is created, query for the earliest date and the latest date, for both 'Next Due' and 'Qty-Want' respectively:

SELECT 
  t1.name, 
  (SELECT fo.due FROM future_orders fo WHERE fo.name = t1.name ORDER BY fo.due ASC LIMIT 1) as 'Next Due',
  (SELECT fo.qty_want FROM future_orders fo WHERE fo.name = t1.name ORDER BY fo.due DESC LIMIT 1) as 'Qty-Want'
FROM table_1 t1
WHERE 1;

This gives you the result you were looking for: next shipment date for the next order that will be uncovered, plus the quantity required to fulfill all the remaining demand. 'C' is returned as NULL because it actually does not require any inventory to be ordered.

user_53050000
Related