How to get difference between two rows for a column field?
SqlSql ServerTsqlSql Server-2005Sql Problem Overview
I have a table like this:
rowInt Value
2 23
3 45
17 10
9 0
....
The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt:
SELECT * FROM myTable ORDER BY rowInt;
This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:
rowInt Value Diff
2 23 22 --45-23
3 45 -35 --10-45
9 0 -45 --0-45
17 10 10 -- 10-0
....
The table is in SQL 2005 (Miscrosoft)
Sql Solutions
Solution 1 - Sql
SELECT
[current].rowInt,
[current].Value,
ISNULL([next].Value, 0) - [current].Value
FROM
sourceTable AS [current]
LEFT JOIN
sourceTable AS [next]
ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)
EDIT:
Thinking about it, using a subquery in the select (ala Quassnoi's answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have...
EDIT2:
I still see this garnering votes, though it's unlikely many people still use SQL Server 2005.
If you have access to Windowed Functions such as LEAD(), then use that instead...
SELECT
RowInt,
Value,
LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value
FROM
sourceTable
Solution 2 - Sql
SELECT rowInt, Value,
COALESCE(
(
SELECT TOP 1 Value
FROM myTable mi
WHERE mi.rowInt > m.rowInt
ORDER BY
rowInt
), 0) - Value AS diff
FROM myTable m
ORDER BY
rowInt
Solution 3 - Sql
SQL Server 2012 and up support LAG / LEAD functions to access the previous or subsequent row. SQL Server 2005 does not support this (in SQL2005 you need a join or something else).
A SQL 2012 example on this data
/* Prepare */
select * into #tmp
from
(
select 2 as rowint, 23 as Value
union select 3, 45
union select 17, 10
union select 9, 0
) x
/* The SQL 2012 query */
select rowInt, Value, LEAD(value) over (order by rowInt) - Value
from #tmp
LEAD(value) will return the value of the next row in respect to the given order in "over" clause.
Solution 4 - Sql
If you really want to be sure of orders, use "Row_Number()" and compare next record of current record (take a close look at "on" clause)
T1.ID + 1 = T2.ID
You are basically joining next row with current row, without specifying "min" or doing "top". If you have a small number of records, other solutions by "Dems" or "Quassanoi" will work fine.
with T2 as (
select ID = ROW_NUMBER() over (order by rowInt),
rowInt, Value
from myTable
)
select T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Value
from ( SELECT ID = ROW_NUMBER() over (order by rowInt), *
FROM myTable ) T1
left join T2 on T1.ID + 1 = T2.ID
ORDER BY T1.ID
Solution 5 - Sql
Does SQL Server support analytic functions?
select rowint,
value,
value - lag(value) over (order by rowint) diff
from myTable
order by rowint
/
Solution 6 - Sql
select t1.rowInt,t1.Value,t2.Value-t1.Value as diff
from (select * from myTable) as t1,
(select * from myTable where rowInt!=1
union all select top 1 rowInt=COUNT(*)+1,Value=0 from myTable) as t2
where t1.rowInt=t2.rowInt-1
Solution 7 - Sql
Query to Find the date difference between 2 rows of a single column
SELECT
Column name,
DATEDIFF(
(SELECT MAX(date) FROM table name WHERE Column name < b. Column name),
Column name) AS days_since_last
FROM table name AS b
Solution 8 - Sql
I'd just make a little function for that. Toss in the two values you need to know the difference between and have it subtract the smaller from the larger value. Something like:
CREATE FUNCTION [dbo].[NumDifference]
( @p1 FLOAT,
@p2 FLOAT )
RETURNS FLOAT
AS
BEGIN
DECLARE @Diff FLOAT
IF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1
RETURN @Diff
END
In a query to get the difference between column a and b:
SELECT a, b, dbo.NumDifference(a, b) FROM YourTable