-6

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 
2
  • 1
    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.
    – Andriy M
    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?
    – Hannah Vernon
    Dec 22, 2015 at 16:00

2 Answers 2

4

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.

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.