read the current row and previous row & calculate difference reporting values over

  • I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).

    ID DEVICE VALUE

    9 456 70

    8 456 60

    7 123 70

    6 123 60

    5 456 50

    4 456 10

    3 123 50

    2 123 20

    1 123 10

    Script would return

    ID 3

    ID 5

    Large table to be queried.

    Thank you for any help.

    Scott

  • scott_lotus (8/15/2012)


    I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).

    ID DEVICE VALUE

    9 456 70

    8 456 60

    7 123 70

    6 123 60

    5 456 50

    4 456 10

    3 123 50

    2 123 20

    1 123 10

    Script would return

    ID 3

    ID 5

    Large table to be queried.

    Thank you for any help.

    Scott

    --==First, create some sample data ==--

    SELECT ID, DEVICE, VALUE

    INTO #yourSampleTable

    FROM (VALUES(9, 456, 70),(8, 456, 60),(7, 123, 70),

    (6, 123, 60),(5, 456, 50),(4, 456, 10),

    (3, 123, 50),(2, 123, 20),(1, 123, 10)

    )a(ID, DEVICE, VALUE);

    --== Now for a solution ==--

    SELECT a.ID, a.DEVICE, a.VALUE

    FROM (SELECT ID, DEVICE, VALUE,

    ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos

    FROM #yourSampleTable) a

    LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,

    ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos

    FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1

    WHERE a.VALUE-b.VALUE > 20;

    Returns: -

    ID DEVICE VALUE

    ----------- ----------- -----------

    3 123 50

    5 456 50


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • This has been solved in other posts and seems to be a very common issue.

    Here's one solution (I added DDL and sample data in an usable format and you should try to do this for your next posts, as it will give you better and faster answers).

    DECLARE @Tabletable(

    idint,

    device int,

    value int)

    INSERT @Table

    SELECT 9, 456, 70 UNION ALL

    SELECT 8, 456, 60 UNION ALL

    SELECT 7, 123, 70 UNION ALL

    SELECT 6, 123, 60 UNION ALL

    SELECT 5, 456, 50 UNION ALL

    SELECT 4, 456, 10 UNION ALL

    SELECT 3, 123, 50 UNION ALL

    SELECT 2, 123, 20 UNION ALL

    SELECT 1, 123, 10 ;

    WITH CTE AS (

    SELECT id,

    device,

    value,

    ROW_NUMBER() OVER(ORDER BY device, id) rownum

    FROM @Table)

    SELECT a.id

    FROM CTE a

    JOIN CTE b ON a.rownum = b.rownum + 1

    WHERE a.value - b.value > 20

    EDIT: See what I meant when saying this is only one solution? Now you have three different ones but one could have problems with missing ids. And mine had probably a problem with the order (corrected now).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Next time pleas include a small script that creates the table and inserts data instead of drawing a table. This will save time for anyone that tries to help you.

    declare @tbl table (ID int, DEVICE int, VALUE int)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (9, 456, 70)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (8, 456, 60)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (7, 123, 70)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (6, 123, 60)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (5, 456, 50)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (4, 456, 10)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (3, 123, 50)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (2, 123, 20)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (1, 123, 10)

    select t2.ID

    from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1

    where t2.VALUE - t1.VALUE > 20

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • scott_lotus (8/15/2012)


    I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).

    ID DEVICE VALUE

    9 456 70

    8 456 60

    7 123 70

    6 123 60

    5 456 50

    4 456 10

    3 123 50

    2 123 20

    1 123 10

    Script would return

    ID 3

    ID 5

    Large table to be queried.

    Thank you for any help.

    Scott

    There is no such thing as current row and previous row in a SQL table.

    What do you mean by current row per device? Is the one with the largest ID?

    How did you manage to calculate 3 and 5? Based on what?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Luis Cazares (8/15/2012)


    DECLARE @Tabletable(

    idint,

    device int,

    value int)

    INSERT @Table

    SELECT 9, 456, 70 UNION ALL

    SELECT 8, 456, 60 UNION ALL

    SELECT 7, 123, 70 UNION ALL

    SELECT 6, 123, 60 UNION ALL

    SELECT 5, 456, 50 UNION ALL

    SELECT 4, 456, 10 UNION ALL

    SELECT 3, 123, 50 UNION ALL

    SELECT 2, 123, 20 UNION ALL

    SELECT 1, 123, 10 ;

    WITH CTE AS (

    SELECT id,

    device,

    value,

    ROW_NUMBER() OVER(ORDER BY id) rownum

    FROM @Table)

    SELECT a.id

    FROM CTE a

    JOIN CTE b ON a.rownum = b.rownum + 1

    WHERE a.value - b.value > 20

    This will only work if the ID for each DEVICE is always next to the previous DEVICE.

    Try this sample data instead: -

    DECLARE @Table TABLE (id INT, device INT, value INT);

    INSERT @Table

    SELECT 5, 456, 50

    UNION ALL SELECT 4, 456, 10

    UNION ALL SELECT 3, 123, 50

    UNION ALL SELECT 2, 456, 20

    UNION ALL SELECT 1, 123, 45;

    Expected result would be ID 5, because it is the only one where the previous ID for the device is more than 20. Your code would instead report back 3 and 5.

    If the OP does only want the previous row, then you'd be better off using the ID column instead of a ROW_NUMBER.

    Adi Cohn-120898 (8/15/2012)


    declare @tbl table (ID int, DEVICE int, VALUE int)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (9, 456, 70)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (8, 456, 60)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (7, 123, 70)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (6, 123, 60)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (5, 456, 50)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (4, 456, 10)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (3, 123, 50)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (2, 123, 20)

    insert into @tbl (ID, DEVICE, VALUE) VALUES (1, 123, 10)

    select t2.ID

    from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1

    where t2.VALUE - t1.VALUE > 20

    i

    Same reasons as above for why this wouldn't work, unless the OP wants to ignore the device.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the reply , will give the above a go.

    Sorry for poor explanation.

    Current row = the row i am querying , bearing in mind i need to query all rows per device.

    Previous row = the row prior to the one i am looking at per device.

    Arrived at 3 and 5 because when reading row 5 the value is > 20 when compared to the previous (row 4) for that devices.

    Sorry , finding it hard to explain , easier to show.

    Scott

    Edit to say, not sure is UNION is a good idea, find it very slow on + billions records.

  • I agree with you Cadavre, but I realized that after posting and realizing the OP was saying devices instead of ids. I corrected it before I saw your new post.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • scott_lotus (8/15/2012)


    Edit to say, not sure is UNION is a good idea, find it very slow on + billions records.

    The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).

    The actual solutions on offer are as follows: -

    Cadavre

    SELECT a.ID, a.DEVICE, a.VALUE

    FROM (SELECT ID, DEVICE, VALUE,

    ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos

    FROM #yourSampleTable) a

    LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,

    ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos

    FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1

    WHERE a.VALUE-b.VALUE > 20;

    Luis Cazares

    WITH CTE AS (

    SELECT id,

    device,

    value,

    ROW_NUMBER() OVER(ORDER BY device, id) rownum

    FROM @Table)

    SELECT a.id

    FROM CTE a

    JOIN CTE b ON a.rownum = b.rownum + 1

    WHERE a.value - b.value > 20;

    Adi Cohn-120898

    select t2.ID

    from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1

    where t2.VALUE - t1.VALUE > 20


    --EDIT--

    Luis Cazares (8/15/2012)


    I agree with you Cadavre, but I realized that after posting and realizing the OP was saying devices instead of ids. I corrected it before I saw your new post.

    Yep, that fixes the issue with your code. Amusingly it now produces the exact same execution plan as mine 😉


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • CELKO (8/15/2012)


    Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:

    WITH Corrected_Tests (device_id, test_value, device_test_seq)

    AS

    (SELECT device_id, test_value

    ROW_NUMBER()

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Tests),

    Delta_Test (device_id, test_value, device_test_seq, test_delta)

    AS

    (SELECT device_id, test_value, device_test_seq,

    test_values

    - LAG (test_value)

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Corrected_Tests)

    SELECT device_id, device_test_seq

    FROM Delta_Test

    WHERE test_delta > 20;

    Out of curiosity, why post a SQL Server 2012 solution in a SQL Server 2008 forum?


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).

    I have 2 small remarks:

    1) Don't jump to conclusion:-) I did test my solution but didn't notice that the original poster wanted that per device.

    2) I don't think that the people that try to answer the questions should write the script that creates the table and insert the data. I think that this is something that should be done by the poster that asks the question.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • CELKO (8/15/2012)


    Please post DDL so that other people do not have to guess at keys, data types and constraint when they create the tablet o answer your question. Is this what you meant?

    Create TABLE Tests

    (test_seq INTEGER NOT NULL PRIMARY KEY,

    device_id INTEGER NOT NULL,

    test_value INTEGER NOT NULL);

    INSERT INTO Tests

    VALUES

    (9, 456, 70),

    (8, 456, 60),

    (7, 123, 70),

    (6, 123, 60),

    (5, 456, 50),

    (4, 456, 10),

    (3, 123, 50),

    (2, 123, 20),

    (1, 123, 10);

    I am going to assume that the rest sequence number is how you defined the ordering of the tests results. You did not tell us. Having a global ordering is a really bad idea; each device should have its own sequence (a really bad design would have used an IDENTITY).

    Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:

    WITH Corrected_Tests (device_id, test_value, device_test_seq)

    AS

    (SELECT device_id, test_value

    ROW_NUMBER()

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Tests),

    Delta_Test (device_id, test_value, device_test_seq, test_delta)

    AS

    (SELECT device_id, test_value, device_test_seq,

    test_values

    - LAG (test_value)

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Corrected_Tests)

    SELECT device_id, device_test_seq

    FROM Delta_Test

    WHERE test_delta > 20;

    Mr. Celko, you started off well, then you went off to la la land. This is a SQL Server 2008 forum, therefore suggesting the OP should use CREATE SEQUENCE is simply wrong.

    It really would be helpful if you would restrict your answers to the capabilities available to version of MS SQL Server being used.

  • Adi Cohn-120898 (8/15/2012)


    The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).

    I have 2 small remarks:

    1) Don't jump to conclusion:-) I did test my solution but didn't notice that the original poster wanted that per device.

    2) I don't think that the people that try to answer the questions should write the script that creates the table and insert the data. I think that this is something that should be done by the poster that asks the question.

    Adi

    I have 2 small replies to your small remarks.

    1) I was talking to the original poster (hence why his post was quoted above my comment) and informing him that the reason you have included UNIONs was to test your solution. Not only do I know you tested it, I had actually stated so.

    2) Again, I was talking to the original poster (hence why his post was quoted above my comment) and informing him that he should have included sample data scripts.

    Were your remarks intended to agree with me?


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (8/15/2012)


    CELKO (8/15/2012)


    Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:

    WITH Corrected_Tests (device_id, test_value, device_test_seq)

    AS

    (SELECT device_id, test_value

    ROW_NUMBER()

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Tests),

    Delta_Test (device_id, test_value, device_test_seq, test_delta)

    AS

    (SELECT device_id, test_value, device_test_seq,

    test_values

    - LAG (test_value)

    OVER (PARTITION BY device_id

    ORDER BY test_seq)

    FROM Corrected_Tests)

    SELECT device_id, device_test_seq

    FROM Delta_Test

    WHERE test_delta > 20;

    Out of curiosity, why post a SQL Server 2012 solution in a SQL Server 2008 forum?

    Probably to encourage quicker adoption of new technology. 😉

  • Do ISO standards say anything about:

    - writing readable code?

    - writing code according to the software's version used?

    - reading previous comments?

    - be aware of the requirements in general and not making assumptions?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply