Finding changes between adjacent rows

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 2008 Forums

Transact-SQL (2008)

Finding changes between adjacent rows

Author

Topic

Boxersoft
Starting Member

28 Posts

Posted - 2010-07-07 : 13:42:04

I want to be able to identify rows in a table where a value differs from that in an adjacent row by more than a specified amount (identifying possible spurious values).

For example:

CREATE TABLE [dbo].[Observation](
[ObsTime] [datetime] NOT NULL,
[Temp] [float] NOT NULL,
[Windspeed] [float] NOT NULL,
[Gust] [float] NOT NULL,
[Rain] [float] NOT NULL,
CONSTRAINT [PK_ObsTest] PRIMARY KEY CLUSTERED
(
[ObsTime] ASC
));

INSERT Observation
SELECT '2010-06-01 12:06:00',14,10.3,17.8,123.4 UNION ALL
SELECT '2010-06-01 12:07:00',14.1,10.5,17.8,123.5 UNION ALL
SELECT '2010-06-01 12:08:00',24.7,9.9,17.7,123.5 UNION ALL
SELECT '2010-06-01 12:09:00',13.8,10.3,17.8,123.5 UNION ALL
SELECT '2010-06-01 12:10:00',13.9,9.9,17.7,240.8 UNION ALL
SELECT '2010-06-01 12:11:00',13.8,10.2,17.8,240.9

I would like to be able to select the 12:08 row and both neighbours because their temperatures differ by (let's say) 2.0 degrees. Similarly I'd want the 12:09 and 12:10 rows because their rain rates differ by (say) 2mm.

I'd also like to be able to select for things like 'lowest maximum temperature reached in a day'.

Any suggestions?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-07 : 13:48:23

search for previos row (subject only) in this forum

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137747&SearchTerms=previous,row

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138813&SearchTerms=previous,row

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Boxersoft
Starting Member

28 Posts

Posted - 2010-07-07 : 19:08:40

Excellent, that looks like it will do the job nicely, thanks. As with many of these things, knowing what to search for is often the tricky part!

I'll have a poke around with the other part of the problem ('lowest max temp reached on any single day') when I get some useful data to work on, it might not be too difficult.

Boxersoft
Starting Member

28 Posts

Posted - 2010-07-09 : 07:45:13

Hmmm... I haven't managed to work out how to find records such as 'lowest max temp reached in a day' (i.e., the day with the most pathetic high) and corresponding 'highest min'. Would it be best to start a new topic?

By the way, the format I used in my original posting is my attempt to reproduce the format that I've seen others use in here. I assume it's some kind of standard - it certainly seems a very neat way to allow others to reproduce structure and data for testing. Is there an easy way to get Management Studio to spit sample data out from an existing table in that format? I couldn't find a way and it's a bit fiddly to do by hand.

Boxersoft
Starting Member

28 Posts

Posted - 2010-07-10 : 04:17:54

Ah - got it, just needed to GROUP BY the date part of the observation time along with MAX() and TOP(1) etc.

Still interested in any comments on producing nicely formatted sample data though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources