CODEWITHSUNDEEP
sqlsql-server
Vinjeru BH
Vinjeru BH

Reputation: 47

I want to calculate a percentage change within a column using SQL Server

Forgive me if this has already been addressed elsewhere, I checked but couldn't work for me. I am not very conversant with SQL Server.

I want to calculate the percentage of indices between months Jan - Dec denoted by 01, 02, 03... in my table using SQL Server. I have added what is expected in the column Pct_Change using Excel. See sample below:

M   Year    Indices  Pct_Change

01  2017    190.51  
02  2017    188.99   -0.8
03  2017    190.06    0.6
04  2017    194.24    2.2
05  2017    196.83    1.3
06  2017    196.30   -0.3
07  2017    191.09   -2.7
08  2017    190.42   -0.3
09  2017    194.02    1.9
10  2017    201.20    3.7
11  2017    200.98   -0.1
12  2017    194.43   -3.3
01  2018    197.23    1.4
02  2018    198.20    0.5
03  2018    194.60   -1.8

Any help will be greatly appreciated.

Upvotes: 2

Views: 1394

Answers (1)

Thom A
Thom A

Reputation: 95561

This is a guess, due to the lack of expected results, but perhaps...

WITH VTE AS(
    SELECT *
    FROM (VALUES(01,2017,190.51),  
                (02,2017,188.99),  
                (03,2017,190.06),  
                (04,2017,194.24),  
                (05,2017,196.83),  
                (06,2017,196.30),  
                (07,2017,191.09),  
                (08,2017,190.42),  
                (09,2017,194.02),  
                (10,2017,201.20),  
                (11,2017,200.98),  
                (12,2017,194.43),  
                (01,2018,197.23),  
                (02,2018,198.20),  
                (03,2018,194.60)) V(M, [Year], Indices))
SELECT *,
       (Indices / LAG(Indices) OVER (ORDER BY [Year] ASC, M ASC)) -1 AS Pct_change
FROM VTE;

Edit: OP is, unfortunately, using 2008R2. Personally, I strongly suggest looking at upgrading your version. SQL Server 2008(R2) has less than a year of extended support left now. After that, you will receive no updates for it, including security updates. If you need to be GDPR compliant, then it's a must that you change.

Anyway, you can do this in SQL Server 2008R2 by using a LEFT JOIN to the same table:

WITH VTE AS(
    SELECT *
    FROM (VALUES(01,2017,190.51),  
                (02,2017,188.99),  
                (03,2017,190.06),  
                (04,2017,194.24),  
                (05,2017,196.83),  
                (06,2017,196.30),  
                (07,2017,191.09),  
                (08,2017,190.42),  
                (09,2017,194.02),  
                (10,2017,201.20),  
                (11,2017,200.98),  
                (12,2017,194.43),  
                (01,2018,197.23),  
                (02,2018,198.20),  
                (03,2018,194.60)) V(M, [Year], Indices)),
--The solution. note that you would need your WITH on the next line
--Mine isn't, as I used a CTE to create the sample data.
RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY [Year] ASC, M ASC) AS RN
    FROM VTE)
SELECT R1.M,
       R1.[Year],
       R1.Indices,,
       (R1.Indices / R2.Indices) -1 AS Pct_change
FROM RNs R1
     LEFT JOIN RNs R2 ON R1.RN = R2.RN + 1;

Upvotes: 3

Related Questions