How can I get my calculated SQL Server percentage value to only display two decimals?

Question

I've got this TSQL:

CREATE TABLE #TEMPCOMBINED(
  DESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS (WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0)
);

INSERT INTO #TEMPCOMBINED (DESCRIPTION, 
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.DESCRIPTION, T1.WEEK1USAGE, T2.WEEK2USAGE, 
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE

...which works as I want (displaying values with two decimal places for the "Usage" and "Price" columns) except that the calculated percentage value (PRICEVARIANCEPERCENTAGE) displays values longer than Cyrano de Bergerac's nose, such as "0.0252707581227436823"

How can I force that to instead "0.03" (and such) instead?

As a rule, this is a display formatting issue that should be handled by the application code, not the database. — HABO, Jan 18 '16 at 19:52

score 1 · Answer 1 · answered Jan 18 '16 at 18:03

1

use ROUND function

SELECT ROUND(column_name,decimals) FROM table_name;

answered Jan 18 '16 at 18:03

Nino Mirza Mušić

140
1
4

PRICEVARIANCEPERCENTAGE AS ROUND( (WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0), 2) – Nino Mirza Mušić Jan 18 '16 at 18:05

score 1 · Accepted Answer · edited Jan 18 '16 at 18:17

Try explicitly casting the computed column when you create the table, as below:

CREATE TABLE #TEMPCOMBINED(
  DESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS CAST((WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) AS DECIMAL(18,2))

);

score 1 · Answer 3 · answered Jan 18 '16 at 18:07

1

Using the ROUND() function. Have a look here and here

answered Jan 18 '16 at 18:07

mauro

5,730
2
26
25

How can I get my calculated SQL Server percentage value to only display two decimals?

3 Answers3