T-SQL – Determine the Difference in Values Between Columns in Different Rows

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.

Calculate Difference Between Columns in Different Rows

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! 🙂

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*