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