sql server - SQL query to assign Inventory to upcoming shipments -
i attempting automate manual function of assigning inventory future shipments in order determine when need produce parts again. in simple example:
to clean example little bit:
table shipmentschedule part shipmentnumber qtyneeded 1 1 30 1 2 30 1 3 30 2 1 40 2 2 40 table inventory part qtyinventory 1 40 2 50 i query outputs following. takes inventory amount each part , uses inventory fulfill shipments until inventory runs out.
part shipment qty need inventory used qty produce 1 1 30 30 0 1 2 30 10 20 1 3 30 0 30 2 1 40 40 0 2 2 40 0 40
here 1 way. also, think last row in expected output wrong based off sample dataset...
declare @shipmentschedule table (part int, shipmentnumber int, qtyneeded int) insert @shipmentschedule values (1,1,30), (1,2,30), (1,3,30), (2,1,40), (2,2,40) declare @inventory table (part int, qtyinventory int) insert @inventory values (1,40), (2,50) --if want quantity produce keep incrementing (as running total), need take last one, use select s.part ,s.shipmentnumber ,s.qtyneeded ,case when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) > 0 qtyneeded when qtyneeded + (i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber)) > 0 qtyneeded + (i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber)) else 0 end inventoryused ,case when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) > 0 0 when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) < 0 (i.qtyinventory - (sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber))) * - 1 end qtytoproduce @shipmentschedule s inner join @inventory on i.part = s.part --or if want how many produce per shipment... wrap can use cte ;with cte as( select s.part ,s.shipmentnumber ,s.qtyneeded ,case when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) > 0 qtyneeded when qtyneeded + (i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber)) > 0 qtyneeded + (i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber)) else 0 end inventoryused ,case when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) > 0 0 when i.qtyinventory - sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber) < 0 (i.qtyinventory - (sum(s.qtyneeded) on (partition s.part order s.part, s.shipmentnumber))) * - 1 end qtytoproduce ,row_number() on (partition s.part order s.part, s.shipmentnumber) rn @shipmentschedule s inner join @inventory on i.part = s.part) select c.part ,c.shipmentnumber ,c.qtyneeded ,c.inventoryused ,isnull(c.qtytoproduce - lag(qtytoproduce) on (partition part order part, shipmentnumber),0) qtytoproduce --,isnull(c.qtytoproduce - (select top 1 qtytoproduce cte c.rn -1 = rn , c.part = part order rn desc),0) qtytoproduce --use version avoid using lag cte c results
/*running total*/ +------+----------------+-----------+---------------+--------------+ | part | shipmentnumber | qtyneeded | inventoryused | qtytoproduce | +------+----------------+-----------+---------------+--------------+ | 1 | 1 | 30 | 30 | 0 | | 1 | 2 | 30 | 10 | 20 | | 1 | 3 | 30 | 0 | 50 | | 2 | 1 | 40 | 40 | 0 | | 2 | 2 | 40 | 10 | 30 | +------+----------------+-----------+---------------+--------------+ /*cte each shipment*/ +------+----------------+-----------+---------------+--------------+ | part | shipmentnumber | qtyneeded | inventoryused | qtytoproduce | +------+----------------+-----------+---------------+--------------+ | 1 | 1 | 30 | 30 | 0 | | 1 | 2 | 30 | 10 | 20 | | 1 | 3 | 30 | 0 | 30 | | 2 | 1 | 40 | 40 | 0 | | 2 | 2 | 40 | 10 | 30 | +------+----------------+-----------+---------------+--------------+
Comments
Post a Comment