Partition by multiple columns

Discussion in 'SQL Server' started by slinshot, May 1, 2009.

  1. slinshot

    slinshot New Member

    May 1, 2009
    Likes Received:
    Trophy Points:
    CMP Ref# |Item No.|Ordered Qty| Remaining Open| Received|Sum itemquantity|Needed
    610551 ****121910 ****400 ********400 *******400 ********800 ****-400
    610551 ****301308 ****400 ********400 *******400 ********800 ****-400
    610552 ****121910 ****400 ********400 *******400 ********800 ****-400
    610552 ****301308 ****400 ********400 *******400 ********800 ****-400

    The problem with the above is that im doing remainingopen - sum(itemquantity) over (partition by itemcode),
    ie. remaining open = 400 - sumitemqauantiy = 800 = -400
    the actual remaining amount is 0 but since it looks at the two sales order the calculation is therefore incorrect.

    so as you can see above the item 121910 appears on two sales orders, so when i sum on that item it gives me the wrong quantity needed, which should be 0.

    basically 400 of 121910 is assigned to 610551 and same for 610552 but the sum ignores the sales order number.

    the reason why i need a sum of items received is because they came in more than 1 receipt. for example the 400 are 100 +100 +200 than that really throws the calculation off. so I take the sum of the receipts and subtract it from the qty remaining open to give me the needed amount.

    But as you can see the sum looks globally at all sales orders.

    if I use where salesordernum = '610551' then I have no problem, however I want to look at it globally

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice