Double Check Your Math

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734552

  More actions

  December 23, 2019 at 12:00 am

  #3705598

  Comments posted to this topic are about the item Double Check Your Math

• Doctor Who 2

  SSCrazy Eights

  Points: 8177

  More actions

  December 23, 2019 at 2:11 pm

  #3709961

  I remember writing a SSRS report which had calculations in it. I was guilty of not testing it properly. I released it to production and almost immediately had management on my back, pointing out the fallacy in the presented data. It was my first time learning the lesson that once you produce something that is blatantly wrong, it is very hard to gain the trust of people afterwards.

  Rod

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  December 23, 2019 at 3:07 pm

  #3709969

  It's not bad enough that some of us have problems with the likes of using the wrong kind of rounding or what have you.  SQL Server "helps" us make some mistakes by automatically rounding to 6 decimal digits for us when a certain threshold is hit.  Even Granny's 4 function calculator is smarter than that.

  For example:

  --===== Produces the correct answer
  DECLARE @9s DECIMAL(20,12) = 0.9999999;
  SELECT @9s*@9S;
  GO
  --===== Produces an incorrect rounded answer due to "precision".
  DECLARE @9s DECIMAL(38,12) = 0.9999999;
  SELECT @9s*@9S;
  GO

  You can read all about that super subtle fail-with-no-warning at the following link.  It's a NASTY gotcha' if you're trying to do things like calculate amortization schedules.

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver15

   

   

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)