Syntax error? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax error?

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-29 : 14:15:49

The following code is giving me incorrect calculations. Will someone have a look at it and see if there's a syntax/logic error in here?

t.maxreb (float) = 7.00
t.txvroyalty (float) = 3.00
e.actualqiks (smallint) = 35

According to this code the sum is 4,095.00 when it should be 105.00.


  update A set A.QXREB = B.total
  from EFILE A Inner Join
  (SELECT E.DLRCODE, SUM((T.MAXREB - T.TXVROYALTY)* E.ACTUALQIKS) TOTAL
  FROM EFILE E LEFT OUTER JOIN
  TEMPQIK T ON
  E.DLRCODE = T.DLRCODE
  WHERE T.PRIMSSN IS NOT NULL AND LEFT(DAN, 2) = 'QX' AND    
    (left(T.prepprod, 3) = 'TAX' or T.prepprod = '1040SD' or  T.prepprod = '1040PL' or 
     T.prepprod = 'ENHEDT' or T.prepprod = 'PROEDT' or T.prepprod = 'PROED+' or 
     T.prepprod = 'TV990E' or T.prepprod = '1040IN')GROUP BY E.DLRCODE)B
  ON A.DLRCODE = B.DLRCODE

Thanks for helping!
Teresa

"Someday I'll know enough to help someone else!"

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-29 : 14:35:46

hmmmm.... 4095.00 / 39 = 105.00

There are 39 records! this code calculated each record then summed the total 39 times.

The following code works:


  update tempqik set acctbal = maxreb - txvroyalty where PRIMSSN IS NOT NULL AND LEFT(DAN, 2) = 'QX' AND    
    (left(prepprod, 3) = 'TAX' or prepprod = '1040SD' or  prepprod = '1040PL' or 
     prepprod = 'ENHEDT' or prepprod = 'PROEDT' or prepprod = 'PROED+' or 
     prepprod = 'TV990E' or prepprod = '1040IN')
  
  update tempqik set acctbal = maxreb where PRIMSSN IS NOT NULL AND LEFT(DAN, 2) = 'QX' AND 
    PREPPROD IS NULL

  update A set A.QXREB = B.total
  from EFILE A Inner Join
  (SELECT E.DLRCODE, SUM(T.ACCTBAL) TOTAL
  FROM EFILE E LEFT OUTER JOIN
  TEMPQIK T ON
  E.DLRCODE = T.DLRCODE
  WHERE T.DLRCODE IS NOT NULL GROUP BY E.DLRCODE)B
  ON A.DLRCODE = B.DLRCODE

If you know of a better way to do this, let me know!
Thanks again,
Teresa

"Someday I'll know enough to help someone else!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources