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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » read the current row and previous row &...

21 posts, Page 1 of 3

1 2 3 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

scott_lotus

Posted Wednesday, August 15, 2012 8:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 20, 2013 1:43 PM
Points: 88, Visits: 373

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

Cadavre

Posted Wednesday, August 15, 2012 8:35 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 2,236, Visits: 6,486

scott_lotus (8/15/2012)

--==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" Wink

Post #1345304

Luis Cazares

Posted Wednesday, August 15, 2012 8:36 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 958, Visits: 1,917

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1345305

Adi Cohn-120898

Posted Wednesday, August 15, 2012 8:36 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:07 PM
Points: 2,018, Visits: 4,911

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

Eugene Elutin

Posted Wednesday, August 15, 2012 8:39 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 2,525, Visits: 4,324

scott_lotus (8/15/2012)

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1345308

Cadavre

Posted Wednesday, August 15, 2012 8:44 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 2,236, Visits: 6,486

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

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

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1345311

scott_lotus

Posted Wednesday, August 15, 2012 8:46 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 20, 2013 1:43 PM
Points: 88, Visits: 373

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

Luis Cazares

Posted Wednesday, August 15, 2012 8:49 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 958, Visits: 1,917

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1345314

Cadavre

Posted Wednesday, August 15, 2012 8:50 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 2,236, Visits: 6,486

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 Wink

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1345316

CELKO

Posted Wednesday, August 15, 2012 9:18 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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 »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions