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:
The first CTE,
NextGreater, finds the ID of the first row that comes after the current row and has
Valuegreater 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
Rangesrecursive 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
The main query takes the output of
Rangesand 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
StartIDand less than or equal to
EndID) and second time to get the
StartIDrows only. (The second join is an outer one to prevent filtering out the 0..1 range.)
This way each
StartIDrow is joined with all the other rows in the same range, so you can calculate the difference between the starting row’s
Valueand that of each of the others. For the initial range, the difference naturally ends up NULL, because the
StartIDof 0 does not exist and the corresponding
Valuein 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.