SQL Derived Value

Results 1 to 2 of 2

Thread: SQL Derived Value

  1. #1
    Join Date
    Dec 1969

    Default SQL Derived Value

    OK,<BR><BR>I&#039;m trying to develop an SQL statement that will help me derive a value from values already existing in my database.<BR><BR>I have four tables.<BR><BR>tblProducts -- Which has <BR>itemID shortDesc <BR><BR>tblInventory --<BR>invID itemID itemQty<BR><BR>tblOrders<BR>orderID orderName orderAddress orderFilled<BR><BR>tblOrders_Products<BR>opID, orderID, itemID, orderQty<BR><BR>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.<BR><BR>When a product is sent out, the quantity is subtracted from tblInventory.itemQty.<BR><BR>What I need to derive is a pendingQty. That is the quantity that is presently on hand minus that which has been ordered but not filled. I can&#039;t seem to come up with an SQL statement that will give me what I&#039;m looking for. I&#039;m sure I just need to SUM(itemQty) ORDER BY itemID, but from there I&#039;m lost.. Thanks in Advance..<BR><BR>Nick

  2. #2
    Johnny Hughes Guest

    Default RE: SQL Derived Value

    I guess you could count the orderFilled = False for each orderID, or better yet create a view that is <BR>Create View pendingOrders<BR>AS<BR>SELECT tblOrders.OrderID, tblOrders_Products.ItemID, sum(tblOrders_Products.orderQty) SumOrdProd<BR>FROM tblOrders_Products, tblOrders<BR>WHERE tblOrders_Products.OrderID = tblOrders.OrderID<BR>AND tblOrders.orderFilled = &#039;False&#039;<BR>GROUP BY tblOrders_Products.orderID, tblOrders_Products.ItemID<BR><BR>You might need to use a 0 for the boolean ... then you could do a select on the view to get the real pendings per Item Number...<BR><BR>SELECT ItemID, sum(SumOrdProd)<BR>FROM pendingOrders<BR>GROUP BY ItemID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts