The difference between the values ​​in consecutive lines for unique identifiers

Question

SQL Server 2008 R2

I need to find the difference between consecutive lines based on a common unique identifier.

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 is what I'm trying to create. I need to find the difference between consecutive lines ONLY IF:

For a specific account number, there is more than 1 line.

---

I managed to delete lines with 1 value / AccountNumber [lines 1 and 4 in this case]

I still need to find a difference from [row-row + 1] I saw a couple of stack overflow answers, 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 is used to start numbering for each value in the group, that is: if you are PARTITION BY AccountNumber , for each unique value of AccountNumber numbering starts at 1. ORDER BY , of course, is used to determine how numbering should be performed , and is required in the function ROW_NUMBER() .

Used in cte, which you can combine by yourself using ROW_NUMBER() to shift the join by 1 record, allowing comparisons between lines.

In SQL Server 2012, the LEAD() and LAG() functions simplify comparisons between rows.