# difference between two rows based upon previous difference value in sql server

I have one table like below

``````ID  Value
1   5
2   6
3   4
4   3
5   7
6   6
7   8
8   9
``````

Output like difference between rows

``````ID  Value  difference
1   5      null
2   6      1
3   4      -2
4   3      -3
5   7      1
6   6      -1
7   8      1
8   9      -1
``````

Scenario for above output:

``````id values difference
1  5      1
2  6
``````

In above case difference >0 so we need to consider id 2,3 values to find difference

``````id values difference
2  6     -2
3  4
``````

In above case difference <=0 so we need to consider id 2,4 values to find difference

``````id values difference
2  6      -3
4  3
``````

In above case difference <=0 so we need to consider id 2,5 values to find difference

``````id values difference
2  6      1
5  7
``````

In above case difference >0 so we need to consider id 5,6 values to find difference

``````id values difference
5  7      -1
6  6
``````

In above case difference <=0 so we need to consider id 5,7 values to find difference

``````id values difference
5  7      1
7  8
``````

In above case difference >0 so we need to consider id 7,8 values to find difference

``````id values difference
7  8      -1
8  9
``````
• I can see some logic in how the differences are obtained, but your explanation makes things more confusing actually. And the last case (the -1 on id 8) is not explained. I would expect the difference on that one to be 1. Dec 21, 2015 at 17:48
• I have to ask, why are you attempting to do this with SQL Server? What is the business case, or is this just for fun? Dec 22, 2015 at 16:00

Regardless of the way the OP formulated the question and the lack of background behind the problem I think a possible solution would be to use a WHILE cycle. First I got a consideration:

The OP mentions that for the last row the result should be -1 which does not makes sense if you follow the original pattern. That in my head would go as:

``````5           = (5-X) Null
|_6         = (6-5)    1
|_4      = (4-6)   -2 (Result is negative so retain 6)
|_3      = (3-6)   -3
|_7      = (7-6)    1 (Drop the 6 since result became positive)
|_6   = (6-7)   -1
|_8   = (8-7)    1
|_9 = (9-8)    1 (Minus 1?, I don't think so)
``````

So I went for a `WHILE` cycle that loops through the rows adding up correlatives by two and storing the result in a variable, then, and depending on the result of the result variable (Positive, Negative) it would either progress by using the last number (positive) or retain the current one (negative) for the next operation.

``````CREATE TABLE #TempTest (id INT IDENTITY(1,1) PRIMARY KEY, num INT);
INSERT INTO #TempTest (num) VALUES (5), (6), (4), (3), (7), (6), (8), (9);

DECLARE
@CurrentNumber INT,
@PastNumber INT = NULL,
@i INT = 1,
@CurrentTotal INT

WHILE (@i<=(SELECT COUNT(*) FROM #TempTest))
BEGIN
SET @CurrentNumber = (SELECT num FROM #TempTest WHERE id = @i)
IF @PastNumber IS NOT NULL
BEGIN
SET @CurrentTotal = (@CurrentNumber - @PastNumber)
IF @CurrentTotal < 0
BEGIN
SET @PastNumber = @PastNumber
END
ELSE
BEGIN
SET @PastNumber = @CurrentNumber
END
END
ELSE
BEGIN
SET @PastNumber = @CurrentNumber
END
IF @CurrentTotal IS NULL
BEGIN
PRINT 'Null'
END
ELSE
BEGIN
PRINT @CurrentTotal
END
SET @i = @i+1
END
``````

This will output: `Null, 1, -2, -3, 1, -1, 1, 1`, and again, the final number would be 1 instead of -1 unlike described in the question. Hopefully the OP will pop along to clarify a bit more.

Here is my take on the problem:

``````WITH NextGreater AS
(
SELECT
t.ID,
NextID = x.ID
FROM
dbo.atable AS t
CROSS APPLY
(
SELECT TOP (1)
ID
FROM
dbo.atable
WHERE
Value > t.Value
AND ID > t.ID
ORDER BY
ID ASC
) AS x
),
Ranges AS
(
SELECT TOP (1)
StartID = 0,
EndID   = ID
FROM
dbo.atable
ORDER BY
ID ASC

UNION ALL

SELECT
StartID = t.ID,
EndID   = t.NextID
FROM
Ranges AS r
INNER JOIN NextGreater AS t ON r.EndID = t.ID
)
SELECT
td.ID,
td.Value,
difference = td.Value - t0.Value
FROM
ranges AS r
INNER JOIN dbo.atable AS td ON td.ID > r.StartID AND td.ID <= r.EndID
LEFT  JOIN dbo.atable AS t0 ON t0.ID = r.StartID
ORDER BY
ID ASC
;
``````

As you can see, it is implemented as a single statement, which usually implies that it uses a set-based approach (which is good, because relational database systems are optimised for that). However, this query cannot really qualify as strictly set-based, because it uses a recursive common table expression (CTE) – a row-by-row device by nature, despite being called "expression".

Anyway, here is a description of how the method works:

1. The first CTE, `NextGreater`, finds the ID of the first row that comes after the current row and has `Value` greater than the current row, for each row in the table. It basically creates a (preliminary) set of ID ranges.

For your example it produces the following results:

``````ID  NextID
1   2
2   5
3   5
4   5
5   7
6   7
7   8
``````
2. The `Ranges` recursive CTE extracts from the previous CTE's result set only the adjacent ranges starting with the row with the lowest ID. It also adds a "zeroth range", one that starts with 0 and ends with the lowest ID. This is the output:

``````StartID  EndID
0        1
1        2
2        5
5        7
7        8
``````
3. The main query takes the output of `Ranges` and joins the original dataset to it twice: first time to get rows with the IDs in each range (more specifically, with the IDs that are greater than `StartID` and less than or equal to `EndID`) and second time to get the `StartID` rows only. (The second join is an outer one to prevent filtering out the 0..1 range.)

This way each `StartID` row is joined with all the other rows in the same range, so you can calculate the difference between the starting row's `Value` and that of each of the others. For the initial range, the difference naturally ends up NULL, because the `StartID` of 0 does not exist and the corresponding `Value` in the joined row set is null.

Because the ranges are adjacent and cover the entire table, the differences are obtained for all the rows.

Depending on one point, not covered by your description, this method could be optimised so as to avoid the recursive CTE (and thus to be promoted to "Pure Set-based"). What I mean is, if values always either decrease or increase sufficiently to exceed the current reference value (which is the case with your example; you are just not specifying whether it is always the case), then the `Ranges` set could be produced in one step like this:

``````WITH Ranges AS
(
SELECT
StartID = 0,
EndID   = (SELECT TOP (1) ID FROM dbo.atable ORDER BY ID ASC)

UNION ALL

SELECT
StartID = MIN(t.ID),
EndID   = x.ID
FROM
dbo.atable AS t
CROSS APPLY
(
SELECT TOP (1)
ID
FROM
dbo.atable
WHERE
Value > t.Value
AND ID > t.ID
ORDER BY
ID ASC
) AS x
GROUP BY
x.ID
)
SELECT
...  -- main query, same as before
``````

On the other hand, since the logic for cases when a value increases only slightly (not exceeding the reference value) is not defined, it is not clear whether either variation would produce the expected output for you. You may want to elaborate on that in your question so that you can get more options to choose from.