Calculate Percent Difference in SQL Server

Question

I have two months with two values, for example:

July-2013  1838.08
Aug-2013   3500.08

How can I calculate the percentage difference in August compared with July?

Answer 1

The formula for this is easy it's (Curr-Prev)*100.0/Prev, what's not clear is how to apply it since we do not know your table definition, contents or keys, and thus do not know how to generically select one month and it's previous value. But, hard-coding it would be like this:

SELECT  
    100.0*(curr.Val - prev.Val) / prev.Val As PercentDiff
FROM yourTable As curr
JOIN yourTable As prev
    ON  curr.MonthStr = 'Aug-2013' AND prev.MonthStr = 'July-2013'

Answer 2

The problem with working out percentage changes is that you need to be careful of when the 'old value' is 0 or you will get an error.

One approach is using nullif(old_Value, 0) but then the problem with that is when old values are 0 you are asking for New_Value/NULL which is the new value. (definitely not the % change)

I worked around it like this:

(case
            when
                OldValue = 0 and NewValue = 0
            then
                cast(0 as Decimal(10,2))
            when
                OldValue = 0
            then
                'Na'
            else
                cast(cast(
                            (
                                (
                                    (cast(NewValue as decimal(11,3)) - 
                                     cast(OldValue as decimal(11,3))
                                    )*100
                                )/cast(OldValue as decimal(11,3))
                            ) as decimal(20,3)
                         ) as varchar
                    )

            end) '% Change' 

I probably threw a lot more casts than necessary but it works well for me. Gives Na when necessary and 0 when necessary.

Answer 3

SELECT VAR(Bonus) 'Variance',
STDEVP(Bonus) 'Standard Deviation',
STDEV(Bonus) 'Standard Deviation',
VARP(Bonus) 'Variance for the Population'
FROM Sales.SalesPerson;

giving credit to fololwing post

http://blog.sqlauthority.com/2008/01/20/sql-server-introduction-to-statistical-functions-var-stdevp-stdev-varp/