I'm trying to develop an SQL statement that will help me derive a value from values already existing in my database.
I have four tables.
tblProducts -- Which has
invID itemID itemQty
orderID orderName orderAddress orderFilled
opID, orderID, itemID, orderQty
I think all of the fileds are self explanitory with the exception of orderFilled, which is a bool and is only true if the products have already been sent out.
When a product is sent out, the quantity is subtracted from tblInventory.itemQty.
What I need to derive is a pendingQty for each item. That is the quantity that is presently on hand minus that which has been ordered but not filled. I can't seem to come up with an SQL statement that will give me what I'm looking for. I'm sure I just need to SUM(itemQty) ORDER BY itemID, but from there I'm lost.. Thanks in Advance..