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.

You tagged this as MySQL but you say you have a SQL 2012 query, what database are you using? — Taryn, Feb 06 '13 at 12:55
Sorry - its MS SQL Server 2012, using server management studio. — user2046878, Feb 06 '13 at 12:59
No problem, we just want to make sure you get the right users looking at your question. :) — Taryn, Feb 06 '13 at 13:00
The difference between `0.70155` and `0.685` is `0.01655` not `0.2`. Are you looking for a ratio? — Martin Smith, Feb 06 '13 at 13:08
Rounding is not necessary. This will be converted to actual percentages using the report builder app once the code is creating the additional column. — user2046878, Feb 06 '13 at 13:10

Martin Smith · Answer 1 · 2013-02-06T13:14:31.707

7

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

edited Feb 06 '13 at 13:14

answered Feb 06 '13 at 13:04

Martin Smith

438,706
87
741
845

'LAG' is not a recognized built-in function name. – user2046878 Feb 06 '13 at 13:21
5

@user2046878 - You aren't on 2012 then. For previous versions you would need to self join on `ROW_NUMBER` or use a recursive CTE. – Martin Smith Feb 06 '13 at 13:22

score 2 · Answer 2 · answered Feb 06 '13 at 14:56

Using CTE and @MartinSmith 's fiddle (DEMO). (note: I have formatted [ds] date for better readability)

;with cte as (
  select [IP_Country], [ds], [Percentage],
         row_number() over (partition by [IP_Country] order by ds) rn
  from YourTable
)
select t1.[IP_Country], convert(date, t1.[ds],102), 
       t1.[Percentage], t2.[Percentage]-t1.[Percentage] movement
from cte t1 left join cte t2 on t1.[IP_Country] = t2.[IP_Country]
          t1.rn - 1 = t2.rn

--Results
IP_COUNTRY  COLUMN_1    PERCENTAGE  MOVEMENT
Australia   2013-01-01  0.70155     (null)
Australia   2013-02-01  0.685       0.01655
Australia   2013-03-01  0.663594    0.021406
Australia   2013-04-01  0.737541    -0.073947
Australia   2013-05-01  0.688212    0.049329
Australia   2013-06-01  0.665384    0.022828
Australia   2013-07-01  0.620253    0.045131
Australia   2013-08-01  0.697183    -0.07693

Aleksandr Fedorenko · Answer 3 · 2013-02-06T15:58:01.083

One more option with OUTER APPLY and EXISTS

SELECT *, t1.Percentage - o.Percentage AS dif
FROM dbo.test36 t1 
OUTER APPLY (
             SELECT t2.Percentage
             FROM dbo.test36 t2
             WHERE t1.IP_Country = t2.IP_Country 
               AND EXISTS (
                           SELECT 1
                           FROM dbo.test36 t3
                           WHERE t1.IP_Country = t3.IP_Country AND t1.ds < t3.ds
                           HAVING MIN(t3.ds) = t2.ds
                           )
             ) o

Demo on SQLFiddle

SQL - How to show the difference between multiple rows results

3 Answers3