Matt Collins 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.

Answer
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.

Comments