Get items that have less available quantity then needed

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

​x
 
OrderId     Item       QtyNeeded----------- ---------- -----------156987      Item1025   72156991      Item1025   55157000      Item125    30156986      Item1250   50156989      Item255    20156997      Item255    99156992      Item3356   90156988      Item659    60156990      Item6598   1156998      Item6599   105156999      Item6600   200156993      Item6600   105156994      Item6602   100156995      Item6603   30156996      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

 
Item       QtyHave---------- -----------Item1025   200Item125    20Item1250   65Item255    110Item3356   100Item659    100Item6598   25Item6599   100Item6600   275Item6602   120Item6603   50Item7809   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

 
OrderId     Item----------- ----------157000      Item125156997      Item255156998      Item6599156993      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

 
OrderId     Item       QtyAvailable----------- ---------- ------------157000      Item125    20156997      Item255    90156998      Item6599   100156993      Item6600   75​

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

 
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) bwhere 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

 
OrderId     Item       QtyAvailable----------- ---------- ------------156999      Item6600   170​

Advertisement

Answer