I have a bunch of calculations in a SQL Server 2012 query, kind of like:
select T1_month ,a.some_value, b.value_to_compare,(select (some_value - value_to_compare)/value_to_compare*100 where value_to_compare != 0) percent_diff from (select T1_month ,sum(some_value) some_value from T1 group by T1_month) a join (select T2_month ,sum(value_to_compare) value_to_compare from T2 group by T2_month) b on a.T1_month = b.T2_month order by T1_month;
I used a round function here, but I need to add a lot more similar lines. Is there any way to just set a global variable to round all columns in one shot? Otherwise it’s just a lot of leg work.
round((some_value - value_to_compare)/value_to_compare*100, 2)
I’ll be pasting to Excel but it would be nice to round it in the source without having to use the round function so many times.
Advertisement
Answer
Here is a workaround, no need to type Round() function for every line:
SELECT 9.0 / 7 * 100 AS Direct_Query CREATE TABLE #Table_1 ( [Column_1] [NUMERIC](18, 2) NULL ) INSERT INTO #Table_1 SELECT 9.0/7 * 100 SELECT Column_1 AS Temp_Table_Formatted FROM #Table_1
Edit:
If you cannot use temp table, you can wrap your query with CTE then just Round() the result of CTE, which is pretty easy to do with help of a multiline editor like Sublime Text or VisualStudio Code:
WITH CTE_Result_To_Format AS ( SELECT T1_month ,a.some_value ,b.value_to_compare ,( SELECT (some_value - value_to_compare) / value_to_compare * 100 WHERE value_to_compare != 0 ) percent_diff FROM ( SELECT T1_month ,SUM(some_value) some_value FROM T1 GROUP BY T1_month ) a JOIN ( SELECT T2_month ,SUM(value_to_compare) value_to_compare FROM T2 GROUP BY T2_month ) b ON a.T1_month = b.T2_month ) SELECT r.T1_month ,ROUND(r.some_value, 2) AS some_value ,ROUND(r.value_to_compare, 2) AS value_to_compare ,ROUND(r.percent_diff, 2) AS percent_diff FROM CTE_Result_To_Format r ORDER BY r.T1_month