How to Calculate the Difference Between Consecutive Row Values in SQL Server 2005?

Calculating Differences Between Sequential Rows in SQL Server 2005

This guide demonstrates how to compute the difference between consecutive row values within a SQL Server 2005 table. The solution utilizes a self-join to efficiently achieve this calculation.

Here's the SQL query:

<code class="language-sql">SELECT
   c.rowInt,
   c.Value,
   ISNULL(n.Value, 0) - c.Value AS Diff
FROM
   sourceTable AS c
LEFT JOIN
   sourceTable AS n
      ON n.rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > c.rowInt)</code>

This query performs a left join of the sourceTable (aliased as c for "current") with itself (aliased as n for "next"). The join condition n.rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > c.rowInt) finds the next row based on the rowInt column, ensuring the correct order. ISNULL(n.Value, 0) handles cases where there's no next row, preventing errors. The difference is calculated as n.Value - c.Value and aliased as Diff.

Example:

Consider this sample sourceTable:

rowInt	Value
2	23
3	45
9	0
17	10

The query's output would be:

rowInt	Value	Diff
2	23	22
3	45	-45
9	0	10
17	10	0

The Diff column shows the difference between the Value of the current row and the Value of the next row. Note that the last row's difference is 0 because there is no subsequent row.

The above is the detailed content of How to Calculate the Difference Between Consecutive Row Values in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!