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?

Answer 1

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))

);

Answer 2

use ROUND function

SELECT ROUND(column_name,decimals) FROM table_name;

Answer 3

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