Difference between values in consecutive rows for unique ID's

Question

SQL Server 2008 R2.

I need to find the difference between consecutive rows based on a common, unique ID.

Data:

AccountNumber   ValueDate     CustomerName        Amount           Difference

     1           06/01/2014   Customer Name 1   -3436.184178    
     2           06/03/2014   Customer Name 2   -154.5               -51.5
     2           06/15/2014   Customer Name 2   -103    
     3           06/02/2014   Customer Name 3   -45289.44   
     4           06/20/2014   Customer Name 4   -4907.52             -1116.43
     4           06/25/2014   Customer Name 4   -3791.09             -3791.09
     4           06/30/2014   Customer Name 4   -3302.19    

The difference column is what I'm trying to generate. I need to find the difference between consecutive rows ONLY IF:

There is more than 1 row for a particular AccountNumber.

---

I managed to remove the rows with 1 value/AccountNumber [rows 1 and 4 in this case]

I still need to find the difference from [row - row + 1] I saw a couple of answers on Stack overflow but they don't seem to apply to this scenario.

Answer 1

You can do this with the ROW_NUMBER() function:

;with cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY ValueDate) AS RN 
              FROM YourTable)
SELECT a.*,a.Amount - b.Amount AS Diff
FROM cte a
LEFT JOIN cte b
  ON a.AccountNumber = b.AccountNumber
  AND a.RN = b.RN -1

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is optional, but used to start the numbering over for each value in a group, ie: if you PARTITION BY AccountNumber then for each unique AccountNumber value the numbering would start over at 1. ORDER BY of course is used to define how the numbering should go, and is required in the ROW_NUMBER() function.

Used in a cte you can then self-join using the ROW_NUMBER() to offset the join by 1 record, allowing comparison between rows.

In SQL Server 2012 the LEAD() and LAG() functions allow for simpler cross-row comparisons.