Have you ever needed to calculate the difference between columns in two or more rows in a table using a T-SQL query? For example you might want to run some trend analysis or audit data for changes. Thankfully using ROW_NUMBER() and Common Table Expressions (CTE’s), this can easily be achieved.
--create our table DECLARE @TableRows TABLE ( DateAdded SMALLDATETIME, Points TINYINT ); --insert some data INSERT INTO @TableRows VALUES ('20150615 15:11:05',10), ('20150615 16:04:23',15), ('20150615 16:23:01',21), ('20150615 16:30:50',22), ('20150615 17:04:07',30); --set up a CTE which we will perform a self join on WITH ExampleCTE AS (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS RowNum , DateAdded , Points FROM @TableRows) --now query the CTE using the self join to get our result SELECT t1.DateAdded , t2.DateAdded , t1.Points , t2.Points , t1.Points - t2.Points AS PointsDifference FROM ExampleCTE t1 LEFT JOIN ExampleCTE t2 ON T1.RowNum = T2.RowNum + 1
The resulting output above shows the calculated column “PointsDifference” containing the difference between the current row and the row previous to it.
The magic happens in the LEFT JOIN part of the query. By adding 1 to the value of the RowNum column, the resulting calculation within the join allows the analysis between the “Points” columns to be accurately returned.
So simple, so effective. I love T-SQL! 🙂