Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT soo.CompanyID ,
- soo.OrderNbr ,
- soo.OrderType ,
- soo.OrderDate ,
- SUM(line.CuryLineAmt) AS 'AmountofBill' ,
- SUM(line.OrderQty) AS 'QuantityofBill' ,
- --intr.RefNbr ,
- SUM(intr.Qty * intr.UnitPrice) AS 'AmountofINTran' ,
- SUM(intr.Qty) AS 'QuantityofINTran'
- FROM dbo.SOOrder soo
- INNER JOIN dbo.SOLine line ON line.CompanyID = soo.CompanyID
- AND line.OrderNbr = soo.OrderNbr
- AND line.OrderType = soo.OrderType
- LEFT JOIN dbo.INTran intr ON intr.CompanyID = line.CompanyID
- AND intr.SOOrderNbr = line.OrderNbr
- AND intr.SOOrderType = line.OrderType
- AND intr.SOOrderLineNbr = line.LineNbr
- WHERE soo.Status != 'L'
- AND datediff(day, soo.OrderDate,getdate())<15
- AND soo.OrderNbr NOT IN (
- SELECT cm.UsrOriginDoc
- FROM dbo.SOOrder cm
- WHERE cm.CompanyID = soo.CompanyID
- AND cm.OrderType = 'CM'
- AND cm.Status = 'C'
- AND datediff(day, cm.OrderDate,getdate())<15 )
- AND soo.OrderType != 'CM'
- GROUP BY soo.CompanyID ,
- soo.OrderType ,
- soo.OrderNbr ,
- soo.OrderDate
- --intr.RefNbr
- HAVING ( ( SUM(line.CuryLineAmt) != SUM(intr.Qty * intr.UnitPrice) )
- OR ( SUM(intr.Qty) != SUM(line.OrderQty) )
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement