sql server - SQL - How to show the difference between multiple rows results -

sql server - SQL - How to show the difference between multiple rows results -

i have sql 2012 query gives me next results:

ip_country ds percentage ------------------------------------- commonwealth of australia 01/01/2013 0.70155 commonwealth of australia 02/01/2013 0.685 commonwealth of australia 03/01/2013 0.663594 commonwealth of australia 04/01/2013 0.737541 commonwealth of australia 05/01/2013 0.688212 commonwealth of australia 06/01/2013 0.665384 commonwealth of australia 07/01/2013 0.620253 commonwealth of australia 08/01/2013 0.697183

the results go on show different countries same dates , different percentages.

what need show, motion of percentages between dates same country only.

so between 02/01 , 01/01 difference 0.02 - can extract info , in excel, ideally have results come out motion in query.

you can utilize lag , lead access previous , next rows.

select *, lag([percentage]) on (partition [ip_country] order [ds]) - [percentage] diff, ([percentage] - lead([percentage]) on (partition [ip_country] order [ds])) / [percentage] [ratio] yourtable

sql fiddle

sql sql-server sql-server-2012 sliding-window