Geek asks and answers

SQL - How to show the difference between multiple row results

I have a 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

Results continue to show different countries for the same dates and different percentages.

What I need to show is the movement of these percentages between dates only for the same Country.

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

+5
source share
3 answers

You can use LAGboth LEADto access the previous and next lines.

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

+7
source

CTE @MartinSmith (DEMO). (: [ds] )

;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
+2

OUTER APPLY 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

0
source

More articles:


All Articles