Comparing sorted rows.....help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing sorted rows.....help

Author

Topic

robert135
Starting Member

5 Posts

Posted - 2004-06-27 : 18:38:30

[code]
time data invalid
1:00 1 NULL
1:02 3 NULL
1:04 2 NULL <---- call Row a
1:06 567 NULL <---- call Row b
1:08 8 NULL <---- call Row c

[/code]

I have to validate data based upon rates of change in the data per period of time. So within a 5 minute interval if the data change is > 200 AND the data change between the first AND the 3rd next point < 200 then mark point 2 as invalid.

So basically I need to do this (not including the date range check)

if( abs(abs(row a(data)) - abs(row b(data))) > 200 && abs(abs(row a(data)) - abs(row c(data))) < 200)
Update row B as invalid.

Now that is the logic.... but I am new to T-SQL so I am not sure if I can use something like a cursor to do this? or if there is a more elegant SQL-ish way to do this. I can do it programatically, but that means an outside program to validate for only this one validation operation and that doesn't seem very elegant.

Any suggestions and possible examples would be great...

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-27 : 18:45:12

update tb
set inavlid = 'yes'
from tbl t1
join tbl1 t2
on t2.time = (select min(t4.time) from tbl t4 where t4.time > t1.time)
join tbl1 t3
on t4.time = (select min(t4.time) from tbl t4 where t4.time > t2.time)
where abs(abs(t2.data) - abs(t1.data)) > 200
and abs(abs(t3.data) - abs(t1.data)) < 200

I have left the abs as you have them but it doesn't cater for + to - changes i.e +150 to -150 will be ok.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robert135
Starting Member

5 Posts

Posted - 2004-06-27 : 19:02:11

Wow, great answer.

I am not thinking in database terms it seems ...

and I appreciate you catching the logic error.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-27 : 19:02:40

Here are some suggestions:

To find the out of range values (second requirement), you can use the following as a base:

select t.time, (select top 1 data from TestTable where time< t.time order by time desc) as previous,
t.data,
(select top 1 data from TestTable where time> t.time order by time) as next
from TestTable t

You could possibly make this into a view then use the view as a basis for your next query - might make things a bit neater.

For the 5 minute problem, you can use something similar to:

select t.time, t.data,
( select top 1 data
from TestTable
where time between DateAdd(mi, -5, t.time) and DateAdd(mi, 5, t.time)
and abs(data - t.data) > 200
) as outofrangeval
from TestTable t

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources