Matt Collins - 5 months ago 22
SQL Question

# Calculate percent change across rows in SQL

I know this has been asked before but none of the answers made any sense to me. Hoping someone can explain more clearly.

My data:

``````year    quarter month   item    sales   quantity    month_number
2011        1   January    a    4250    85000       1
2011        1   February   a    4600    92000       2
2011        1   March      a    3700    74000       3
2011        2   April      a    4215    84300       4
2011        2   May        a    5120    102400      5
2011        2   June       a    5010    100200      6
2011        3   July       a    4690    93800       7
2011        3   August     a    4900    98000       8
2011        3   September  a    5400    108000      9
2011        4   October    a    5820    116400      10
2011        4   November   a    5900    118000      11
2011        4   December   a    5730    114600      12
2011        1   January    b    1417    35417       1
2011        1   February   b    1533    38333       2
2011        1   March      b    1233    30833       3
2011        2   April      b    1405    35125       4
2011        2   May        b    1707    42667       5
2011        2   June       b    1670    41750       6
2011        3   July       b    1563    39083       7
2011        3   August     b    1633    40833       8
2011        3   September  b    1800    45000       9
2011        4   October    b    1940    48500       10
2011        4   November   b    1967    49167       11
2011        4   December   b    1910    47750       12
``````

I'm trying to create a new column at the end that contains the percent change for sales from month to month for each item (a & b). So, both month_number 1s would be a null value because there would be no change. But month_number 2 should be equal to (4600 - 4250)/4250, which is 0.082.
And so on.

Any thoughts? Thanks in advance.

``````SELECT
t1.*
,CASE
WHEN t2.sales IS NULL THEN NULL
ELSE (t1.sales - t2.sales) / (t2.sales * 1.00)
END AS MonthOverMonth
FROM
#TblName t1
LEFT JOIN #TblName t2
ON t1.[Year] = t2.[Year]
AND t1.month_number - 1 = t2.month_number
AND t1.item = t2.item
``````

Gordon has the LAG answer the way I was thinking to, but I was writing the non window function version in case you said mysql of something. Anyway, LAG answer which is in SQL 2012 + . `SELECT @@VERSION` should tell you which MSSQL version you are using. But even without it you can just do a self join to the preceding month and do your calculation.

Source (Stackoverflow)