-1

I am little stumped on what to do with this query. The POs attached to them should only total as 1600 left open of that part number. However, instead it generates multiple rows unnecessarily. The problem appears to be the inventory ordered column/section of the query. I have tried using sum() over partition by as well to no avail.

The result is: First Try

The code is:

SELECT DISTINCT i.classid, 
                l.whseloc, 
                l.invtid, 
                l.qtyonhand, 
                Sum(CASE 
                      WHEN f.mrpflag = 0 THEN l.qtyonhand 
                      ELSE 0 
                    END) 
                  OVER ( 
                    partition BY l.invtid) AS TotalNonNet, 
                ( p.qtyord - p.qtyrcvd )   AS InventoryOrdered, 
                Sum(l.qtyonhand * f.mrpflag) 
                  OVER( 
                    partition BY l.invtid) AS TotalNet 
FROM   opslocation l (nolock) 
       JOIN opsinventory i (nolock) 
         ON l.invtid = i.invtid 
       JOIN opsamloctable f (nolock) 
         ON l.whseloc = f.whseloc 
       LEFT JOIN dbo.opspurorddet (nolock) p 
              ON l.invtid = p.invtid 
WHERE  l.qtyonhand <> 0 

The result I want is:

Result Wanted

I appreciate the help.

2

You need to use a regular SUM with GROUP BY, instead of a windowed function:

SELECT  i.classid,
        l.whseloc,
        l.invtid,
        l.qtyonhand,
        SUM(CASE WHEN f.MRPFlag = 0 THEN l.qtyonhand ELSE 0 END) TotalNonNet,
        SUM(p.qtyord-p.qtyrcvd) InventoryOrdered,
        SUM(l.qtyonhand * f.mrpflag) TotalNet
FROM opslocation l (nolock)
INNER JOIN opsinventory i (nolock) 
    ON l.invtid = i.invtid
INNER JOIN opsamloctable f (nolock) 
    ON l.whseloc = f.whseloc
LEFT JOIN dbo.opspurorddet (nolock) p 
    ON l.InvtID = p.InvtID
WHERE l.qtyonhand <> 0
GROUP BY i.classid,
         l.whseloc,
         l.invtid,
         l.qtyonhand;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.