CODEWITHSUNDEEP
sqlsql-serverexcelsumcalculated-field
Ridiculoushavoc
Ridiculoushavoc

Reputation: 13

Trying to Calculate Month over Month percentage increase/decrease

I am trying to calculate Month over Month % change on data rows. For example my current output is:

DataDate |LocationId|Payment|MoM [Current placeholder column in script]             
12-1-2013|LocationA |$5.00  |      
1-1-2014 |LocationA |$10.00 |    
2-1-2014 |LocationA |$100.00|   
12-1-2013|LocationB |$50.00 |    
1-1-2014 |LocationB |$25.00 |    
2-1-2014 |LocationB |$50.00 |

I am pasting the results into Excel and then calculating the MoM by using the following formula:
((CurrentDataDate Payment/PreviousDataDate Payment)-1)]

I can not figure out where to even begin trying to accomplish this so I cant provide any coding from what i have tried...I have read about and attempted a correlated scalar query used to calculate running totals and tried to alter it to accomplish this...no dice...I tried with a Join and a subquery but i will admit my subquery abilities are less than adequate.

The code used to call this info is:

Declare @BeginDate as DateTime
Declare @EndDate as DateTime    
Set @BeginDate = '12-01-2013'
Set @EndDate = '02-01-2014'

Select DataDate,LocationId,Payment,0 as MoM
From dbo.mytableview
Where DataMonth between @BeginDate and @EndDate

Desired output is:

DataDate |LocationId|Payment|MoM              
12-1-2013|LocationA |$5.00  |      
1-1-2014 |LocationA |$10.00 |1.0  [or 100%]  
2-1-2014 |LocationA |$100.00|9.0  [or 900%] 
12-1-2013|LocationB |$50.00 |    
1-1-2014 |LocationB |$25.00 |-.50 [or -50%]  
2-1-2014 |LocationB |$50.00 |1.0  [or 100%]

I am using Microsoft SQLServer 2008 R2. I also have/and can use the 2012 version if that is needed.

Upvotes: 1

Views: 5655

Answers (3)

John Bustos
John Bustos

Reputation: 19544

Although dean's solution is better, I just wanted to also post a solution for people that don't have SQL Server 2012 for completeness' sake (and since I had already started on it before dean posted his).

This can be accomplished using Common Table Expressions and the Row_Number() function:

WITH CTE AS 
(
SELECT Row_Number() OVER (PARTITION BY locationid ORDER BY datadate) AS RN, datadate, locationid, payment
FROM table
)

SELECT 
    CTE2.*, 
    (CTE2.payment / CTE1.payment) - 1 AS MOM
FROM
    CTE AS CTE1 RIGHT OUTER JOIN
    CTE AS CTE2
ON
    CTE1.RN = CTE2.RN-1
AND
    CTE2.locationid = CTE1.locationid 
ORDER BY
    locationid

Upvotes: 0

dean
dean

Reputation: 10098

Here's another solution, works on earlier versions:

select *, (t2.payment/t1.payment)-1
from #t t1
left join #t t2 on datediff(month, t1.datadate, t2.datadate)=1
and t1.locationid = t2.locationid

However, this self-join solutions generally don't perform well with larger sets of data, and a cursor solution is preferred in such cases.

Upvotes: 0

dean
dean

Reputation: 10098

This works on SQL Server 2012:

with x as (
select datadate, locationid, payment,
    lag(payment) over(partition by locationid order by datadate) as prev_payment
from table
)
select *, (payment/prev_payment)-1
from x

Upvotes: 2

Related Questions