Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

read the current row and previous row & calculate difference reporting values over Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 8:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 8:51 AM
Points: 92, Visits: 393
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

Post #1345294
Posted Wednesday, August 15, 2012 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
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



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345304
Posted Wednesday, August 15, 2012 8:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 3,559, Visits: 7,679
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 @Table	table(
id int,
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1345305
Posted Wednesday, August 15, 2012 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 2,116, Visits: 5,433
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/
Post #1345306
Posted Wednesday, August 15, 2012 8:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1345308
Posted Wednesday, August 15, 2012 8:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
Luis Cazares (8/15/2012)
DECLARE @Table	table(
id int,
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345311
Posted Wednesday, August 15, 2012 8:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 8:51 AM
Points: 92, Visits: 393
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.
Post #1345313
Posted Wednesday, August 15, 2012 8:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 3,559, Visits: 7,679
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1345314
Posted Wednesday, August 15, 2012 8:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
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



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345316
Posted Wednesday, August 15, 2012 9:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:12 PM
Points: 1,945, Visits: 2,937
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;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1345334
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse