I have a list of order items and a list of inventory quantity per item
I want to get the first of each item that I will not have enough inventory
This is the list of order items
OrderId Item QtyNeeded ----------- ---------- ----------- 156987 Item1025 72 156991 Item1025 55 157000 Item125 30 156986 Item1250 50 156989 Item255 20 156997 Item255 99 156992 Item3356 90 156988 Item659 60 156990 Item6598 1 156998 Item6599 105 156999 Item6600 200 156993 Item6600 105 156994 Item6602 100 156995 Item6603 30 156996 Item7809 56
This is the list of inventory
Item QtyHave ---------- ----------- Item1025 200 Item125 20 Item1250 65 Item255 110 Item3356 100 Item659 100 Item6598 25 Item6599 100 Item6600 275 Item6602 120 Item6603 50 Item7809 75
I want to see a list with the first order item which I will not have enough inventory like this:
OrderId Item ----------- ---------- 157000 Item125 156997 Item255 156998 Item6599 156993 Item6600
Even better would be better to see how much is partially available
OrderId Item QtyAvailable ----------- ---------- ------------ 157000 Item125 20 156997 Item255 90 156998 Item6599 100 156993 Item6600 75
Advertisement
Answer
This will return the first order item that does not meet the needed inventory:
select *
from (
select
*,
case
when CumulativeNeededQty <= QtyHave then QtyNeeded
when CumulativeNeededQty - QtyNeeded <= QtyHave then QtyHave - CumulativeNeededQty + QtyNeeded
else 0
end as PartialQty
from (
select
OrderId,
orders.Item,
orders.QtyNeeded,
sum(QtyNeeded) over (Partition by orders.Item order by OrderID) as CumulativeNeededQty,
QtyHave
from orders
join inventory on inventory.Item = orders.Item
) a
) b
where PartialQty <> 0 and PartialQty <> QtyNeeded
And a working SQLFiddle using your example: http://sqlfiddle.com/#!18/f4973e/12
Defining “first” as the smaller OrderId means there is an error in your expected results. OrderID 156993 is before 156999, so the 4th result should actually be
OrderId Item QtyAvailable ----------- ---------- ------------ 156999 Item6600 170