SHARE
TWEET

Untitled

a guest Jun 16th, 2019 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  SELECT  soo.CompanyID ,
  2.             soo.OrderNbr ,
  3.             soo.OrderType ,
  4.             soo.OrderDate ,
  5.             SUM(line.CuryLineAmt) AS 'AmountofBill' ,
  6.             SUM(line.OrderQty) AS 'QuantityofBill' ,
  7.             --intr.RefNbr ,
  8.             SUM(intr.Qty * intr.UnitPrice) AS 'AmountofINTran' ,
  9.             SUM(intr.Qty) AS 'QuantityofINTran'
  10.     FROM    dbo.SOOrder soo
  11.             INNER JOIN dbo.SOLine line ON line.CompanyID = soo.CompanyID
  12.                                           AND line.OrderNbr = soo.OrderNbr
  13.                                           AND line.OrderType = soo.OrderType
  14.             LEFT JOIN dbo.INTran intr ON intr.CompanyID = line.CompanyID
  15.                                          AND intr.SOOrderNbr = line.OrderNbr
  16.                                          AND intr.SOOrderType = line.OrderType
  17.                                          AND intr.SOOrderLineNbr = line.LineNbr
  18.     WHERE   soo.Status != 'L'
  19.             AND datediff(day,  soo.OrderDate,getdate())<15
  20.             AND soo.OrderNbr NOT IN (
  21.             SELECT  cm.UsrOriginDoc
  22.             FROM    dbo.SOOrder cm
  23.             WHERE   cm.CompanyID = soo.CompanyID
  24.                     AND cm.OrderType = 'CM'
  25.                     AND cm.Status = 'C'
  26.                     AND datediff(day,  cm.OrderDate,getdate())<15 )
  27.             AND soo.OrderType != 'CM'
  28.     GROUP BY soo.CompanyID ,
  29.             soo.OrderType ,
  30.             soo.OrderNbr ,
  31.             soo.OrderDate
  32.             --intr.RefNbr
  33.     HAVING  ( ( SUM(line.CuryLineAmt) != SUM(intr.Qty * intr.UnitPrice) )
  34.               OR ( SUM(intr.Qty) != SUM(line.OrderQty) )
  35.             );
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top