Home » difference between two rows based upon previous difference value in sql server

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

Solutons:


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.

Related Solutions

Using var self = this or .bind(this)? [closed]

Things that would favor var self = this; bind isn't supported in IE8 and Safari5. If you aim to build a library or code that supports legacy browsers, then var self = this would be more cross-browser friendly. Sometimes, callbacks are bound to a certain context...

What is the difference between SSL vs SSH? Which is more secure?

SSL and SSH both provide the cryptographic elements to build a tunnel for confidential data transport with checked integrity. For that part, they use similar techniques, and may suffer from the same kind of attacks, so they should provide similar security (i.e....

How can I stop applications and services from running?

First Things First You may have some misconceptions about how Android works and what's really happening when a service is running or an app is in the background. See also: Do I really need to install a task manager? Most apps (e.g., ones you launch manually)...

How do I reset a lost administrative password?

By default the first user's account is an administrative account, so if the UI is prompting you for a password it's probably that person's user password. If the user doesn't remember their password you need to reset it. To do this you need to boot into recovery...

How can I use environment variables in Nginx.conf

From the official Nginx docker file: Using environment variables in nginx configuration: Out-of-the-box, Nginx doesn't support using environment variables inside most configuration blocks. But envsubst may be used as a workaround if you need to generate your...

Difference between .bashrc and .bash_profile

Traditionally, when you log into a Unix system, the system would start one program for you. That program is a shell, i.e., a program designed to start other programs. It's a command line shell: you start another program by typing its name. The default shell, a...

Custom query with Castle ActiveRecord

In this case what you want is HqlBasedQuery. Your query will be a projection, so what you'll get back will be an ArrayList of tuples containing the results (the content of each element of the ArrayList will depend on the query, but for more than one value will...

What is the “You have new mail” message in Linux/UNIX?

Where is this mail? It's likely to be in the spool file: /var/mail/$USER or /var/spool/mail/$USER are the most common locations on Linux and BSD. (Other locations are possible – check if $MAIL is set – but by default, the system only informs you about...

How can I find the implementations of Linux kernel system calls?

System calls aren't handled like regular function calls. It takes special code to make the transition from user space to kernel space, basically a bit of inline assembly code injected into your program at the call site. The kernel side code that "catches" the...

Is a composite index also good for queries on the first field?

It certainly is. We discussed that in great detail under this related question: Working of indexes in PostgreSQL Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not...

Explaining computational complexity theory

Hoooo, doctoral comp flashback. Okay, here goes. We start with the idea of a decision problem, a problem for which an algorithm can always answer "yes" or "no." We also need the idea of two models of computer (Turing machine, really): deterministic and...

Building a multi-level menu for umbraco

First off, no need pass the a parent parameter around. The context will transport this information. Here is the XSL stylesheet that should solve your problem: <!-- update this variable on how deep your menu should be --> <xsl:variable...

How to generate a random string?

My favorite way to do it is by using /dev/urandom together with tr to delete unwanted characters. For instance, to get only digits and letters: tr -dc A-Za-z0-9 </dev/urandom | head -c 13 ; echo '' Alternatively, to include more characters from the OWASP...

How to copy a file from a remote server to a local machine?

The syntax for scp is: If you are on the computer from which you want to send file to a remote computer: scp /file/to/send username@remote:/where/to/put Here the remote can be a FQDN or an IP address. On the other hand if you are on the computer wanting to...

What is the difference between curl and wget?

The main differences are: wget's major strong side compared to curl is its ability to download recursively. wget is command line only. There's no lib or anything, but curl's features are powered by libcurl. curl supports FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP,...