Earliest Available Date - Sum Qty's

Question

I am trying to determine the earliest available date for a item. The records below are examples of open purchase order dates.

If an sales order calls for Qty 150, the earliest date available would be 2018-09-06.

If a sales order called for Qty 160, the earliest date available would be 2018-09-28.

Using the statement below will only return the MAX date value of 2018-10-01, is there a way to grab the correct earliest date based on SUM(po_qty) >= sales_order_qty?

IIF(SUM(po_qty) >= sales_order_qty,MAX(receive_date)) = 2018-10-01


branch item_id  po_qty receive_date
VMXX    93111   50     2018-06-13
VMXX    93111   50     2018-08-02
VMXX    93111   50     2018-09-06
VMXX    93111   100    2018-09-28
VMXX    93111   50     2018-10-01

Answer 1

In most databases, you would use the ANSI-standard cumulative sum functionality:

select min(receive_date)
from (select t.*,
             sum(po_qty) over (partition by branch, item_id order by receive_date) as running_qty
      from t
      where branch = ? and item_id = ?
    ) t
where running_qty >= ?;