Skip to content
Advertisement

can you set environmental variable to round numbers in SQL Server query?

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
2 People found this is helpful
Advertisement