Deriving a value using SQL

OK,

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
itemID shortDesc

tblInventory --
invID itemID itemQty

tblOrders
orderID orderName orderAddress orderFilled

tblOrders_Products
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..

Nick

Comments


  • Hello,

    : What I need to derive is a pendingQty for each item. That is

    Here is one possible solution for your problem:

    [code]
    select itemId, sum("qty")
    from (select ti.itemID, ti.itemQty "qty"
    from balage_tblInventory ti
    union all
    select top.itemId, -top.orderqty "qty"
    from balage_tblOrders tor, balage_tblOrders_products top
    where top.orderID = tor.orderID
    and tor.orderfilled = 0
    )
    group by itemId
    ;
    [/code]

    I had no time to test it. I hope it works. :-)

    Balage

  • try:


    select itemID, itemQty-isnull(TotalOrderedQty,0) pendingQty from ( select A.itemID, A.itemQty, B.TotalOrderedQty from
    (select itemID, sum(itemQty) itemQty from tblInventory group by itemID) as A
    left join
    (select itemID, sum(orderQty) TotalOrderedQty from tblOrders_Products where orderID in (select orderID from tblOrders where orderFilled=0)
    group by itemID) as B
    on A.itemID=B.itemID )
    as C
    order by itemID


    remark:
    orderFilled=0(means false) is T-SQL expression, if you use other DBMS, it should be changed accordingly.





Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories