SQL - How to show the difference between multiple rows results

Question

I have an SQL 2012 query that gives me the following results:

IP_Country  ds          Percentage
-------------------------------------
Australia   01/01/2013  0.70155
Australia   02/01/2013  0.685
Australia   03/01/2013  0.663594
Australia   04/01/2013  0.737541
Australia   05/01/2013  0.688212
Australia   06/01/2013  0.665384
Australia   07/01/2013  0.620253
Australia   08/01/2013  0.697183

The results go on to show different countries for the same dates and different percentages.

What i need to show, is the movement of those percentages between the dates for the same Country only.

So between 02/01 and 01/01 the difference is 0.02 - i can extract the data and do this in excel, but ideally i would like to have the results come out with the movement in the query.

Answer 1

You can use LAG and LEAD to access the previous and next rows.

SELECT *,
        LAG([Percentage]) OVER (PARTITION BY [IP_Country] ORDER BY [ds]) 
                                                               - [Percentage] AS diff,
       ([Percentage] - LEAD([Percentage]) OVER (PARTITION BY [IP_Country] ORDER BY [ds])) 
                                                             / [Percentage] AS [ratio]
FROM  YourTable  

SQL Fiddle