CODEWITHSUNDEEP
sqlsql-server-2008-r2
kittie89us
kittie89us

Reputation: 1

SQL Calculate Month over Month Percentage

I am using SQL Server 2008R2. I am trying to calculated the month over month change in percent format for the following data:

Month      TransCnt
2015-01-01 2830812
2015-02-01 2760239
2015-03-01 3313988
2015-04-01 3355073
2015-05-01 3599517

Previous formula used in Excel was =(b2-b1)/b1. And I am trying to accomplish this:

 Month      TransCnt Monthly Change 
 2015-01-01 3134041 null
 2015-02-01 3058192 -2.42%
 2015-03-01 3659324 19.66%
 2015-04-01 3726863 1.85%
 2015-05-01 4028524 8.09%

Upvotes: 0

Views: 1020

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You need to get the previous value. Well, the easiest thing is to upgrade SQL Server and use the lag() function ;) Oh, maybe that really isn't an option.

Here is an equivalent:

select d.*,
       (1 - d2.transcnt * 1.0 / d.transcnt) as monthly_change
from data d outer apply
     (select top 1 d2.*
      from data d2
      where d2.month < d.month
      order by month desc
     ) dprev;

Upvotes: 1

Related Questions