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?

Do you really want the statistical *Variance* of two numbers? Or do you mean something else? — RBarryYoung, Sep 10 '13 at 14:31
I would like to calculate the percentage that was increase/decrease compared to previous month — rgx71, Sep 10 '13 at 14:33
Ah, that is called "*percentage difference*". "Variance" is an entirely different thing. — RBarryYoung, Sep 10 '13 at 14:34
Your original post and title did say "Variance" (I just checked the Edit log to be sure). — RBarryYoung, Sep 10 '13 at 14:42

score 37 · Accepted Answer · answered Sep 10 '13 at 14:55

37

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'

answered Sep 10 '13 at 14:55

RBarryYoung

55,398
14
96
137

2

Would this be able to be used in a dynamic sense? – Jaskier Oct 24 '18 at 18:11
LAG function https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement – Imran Qadir Baksh - Baloch Nov 02 '22 at 05:42

score 3 · Answer 2 · answered Oct 06 '17 at 11:15

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.

score 1 · Answer 3 · answered Sep 10 '13 at 14:33

1

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/

answered Sep 10 '13 at 14:33

Dhaval

2,801
20
39

4

Note: clarification has changed the question. – RBarryYoung Sep 10 '13 at 14:35
1

@RBarryYoung .. you are right but what we can do .. i upvoted your comment – Dhaval Sep 10 '13 at 15:32

score 0 · Answer 4 · answered Aug 19 '21 at 23:01

to handle zeroes in the denominator, i use:

case 
  when oldVal<>0 then ((newVal-oldVal)*100.0)/oldVal 
  else 
    case when newVal=0 then 0 else Null end 
end

this will return 0 if newVal and oldVal are both 0, and Null if oldVal is zero but newVal is non-zero.

multiplying by 100.0 (as opposed to 100) will implicitly convert int fields into decimal. i find it cleaner than try_convert().

Calculate Percent Difference in SQL Server

4 Answers4