format "Floor" should change to Round

• adisql

  SSCrazy

  Points: 2845

  More actions

  October 9, 2017 at 6:54 pm

  #324070

  Hi,

  Need to get the integer values in results.
  i am using below FLOOR function , but i want to get the values to be rounded.

  Query:
  REPLACE(FORMAT(FLOOR(CASE WHEN sum(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
                  ELSE sum(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.LOANAMOUNT * a.STR_WEIGHT    ELSE 0 END) /
                    sum(CASE WHEN a.LOANAMOUNT IS NOT NULL THEN a.STR_WEIGHT ELSE 0    END)
       END),'C'),'.00','') AS AMOUNT

  Result:

  $13,497.50
  $12,241
  $21,295.75
  $13,916.17
  $12,456.96
  $18,487

  Required result:

  $13,498
  $12,241
  $21,296
  $13,916
  $12,457
  $18,487

  Any help please.

• pietlinden

  SSC Guru

  Points: 63198

  More actions

  October 9, 2017 at 7:42 pm

  #1962929

  Use ROUND?
  SELECT Amount,ROUND(Amount,0) AS rounded
FROM Green;

• adisql

  SSCrazy

  Points: 2845

  More actions

  October 9, 2017 at 7:51 pm

  #1962930

  pietlinden - Monday, October 9, 2017 7:42 PM

  Use ROUND?
  SELECT Amount,ROUND(Amount,0) AS rounded
FROM Green;

  thank you.