Rounding in tsql

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  August 17, 2016 at 12:02 pm

  #1895992

  No I am not missing values I am getting the exact results what I am expecting, I tested.

• Luis Cazares

  SSC Guru

  Points: 183704

  More actions

  August 17, 2016 at 12:23 pm

  #1896001

  I did test as well.

  CREATE TABLE #Test(

  Units decimal(10,5),

  DesiredResult decimal(10,5),

  );

  INSERT INTO #Test SELECT

  0.3, 0.3 UNION ALL SELECT

  0.1, 0.1 UNION ALL SELECT

  1.4, 1.4 UNION ALL SELECT

  1.5, 2 UNION ALL SELECT

  1.7, 2 UNION ALL SELECT

  0.8, 1 UNION ALL SELECT

  0.49, 0.49;

  SELECT *,

  CASE WHEN ROUND(Units,0) = ROUND(Units,0,1)

  THEN Units ELSE ROUND(Units,0)

  END,

  case when (units - floor(units)) < 0.49 then round(units,2)

  when (units - floor(units)) > = 0.50 then round(units,0)

  end

  FROM #Test

  GO

  DROP TABLE #Test

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2