NOTE: I am not making changes to a database. I am creating a report.
The purpose of the report is to show pending orders that need to be assembled for shipment, but not until there is enough stock to fill the order. An order includes multiple inventory items, but the inventory on hand must be >= the ordered amount per each inventory item and in order by oldest date first before the order can be added to the report.
I've written this to where it pulls the orders, but I need it to loop through to the next order and carry over the quantity of inventory On Hand from the calculation prior to this order. When the calculation is < 0, I don't need to see the order.
EXAMPLE OUTPUT:
Order Date | Order No | Item No | Quantity Ordered | On Hand | Available Qty
2015-01-01 123456 555555 50 60 10
2015-01-02 555544 555555 10 10 00
Notice On Hand says 60 for Item No 555555 in the first row. This is the actual QOH, but the report needs to subtract the amount that was ordered in the previous line from my On Hand stock, and give me the remainder, or show the new available total under On Hand. When my On Hand amount can't fulfill an order, I don't want the order to appear on my report. My current report shows On Hand to be 60 in both rows, and instead of zero, like above, it just subtracts 10 from 60, as if it's my only order.
I don't know what approach to take to do this type of incremental change in a field, but I am assuming it involves a loop and a variable (If I need to add a variable, then it needs to begin with the actual Quantity on hand), ???? Could someone please assist me with a direction? My search to answer this has only left me more unsure of how to do this. I can provide the SQL, but it is rather complicated, so I am trying to keep this on a more general level.
"Looping" should be used as a last resort in SQL. You can do so using a CURSOR but they tend to run slower and require more work than standard SQL commands.
I would recommend trying to break this problem down into smaller tables using sub-queries / CTEs (Common Table Expressions). Can you create a query that shows the total on hand amounts for each item number? Now put that into a sub-query and start building on top of it.