group consecutive records - SQL Server Forums

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

Transact-SQL (2000)

group consecutive records

Author

Topic

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 04:03:38

I'm looking for a way to group consecutive records. I have a table called Data with two fields; a primary key 'Time' which is a datetime and 'Value' which is a decimal. See below:

----------------------------------------
Time | Value |
----------------------------------------
2/6/2007 02:05:00.333 3.8
2/6/2007 02:10:00.333 3.8
2/6/2007 02:15:00.333 9.0
2/6/2007 02:20:00.333 9.0
2/6/2007 02:25:00.333 9.0
2/6/2007 02:30:00.333 3.8
2/6/2007 02:35:00.333 3.8

I want to be able to group by min(Time) and Value, but only those records that are consecutive. So from the data set i supplied i should be able to obtain the following data:

----------------------------------------
Time | Value |
----------------------------------------
2/6/2007 02:05:00.333 3.8
2/6/2007 02:15:00.333 9.0
2/6/2007 02:30:00.333 3.8

Any ideas? I've been trying to solve this for ages.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 04:06:54

are you using SQL Server 2000 or 2005. Please post in the correct forum and please do not cross post.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78871

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 04:14:16

quote:
Originally posted by khtan

are you using SQL Server 2000 or 2005. Please post in the correct forum and please do not cross post.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78871

KH

I'm running this database on a sql server 2000 and 2005 server, so whichever i could get help for would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 04:20:46

the should ask in 2000 forum else any solution posted might be only applicable to 2005.

rcurrey
Starting Member

30 Posts

Posted - 2007-02-09 : 09:06:19

Is this really a grouping, or are you trying to find out when the value changes? If it is the latter, the only way that I can think of would be to use a cursor and loop through the records, inserting the current values into a 2nd table when the value is different from the value of the previous record. You would load the 'value' from the record into a variable, get the next record, if they are the same, loop through.

In pseudocode:

define and open cursor - Select time,value from table
set @var = value
while not at end
if @var <> value
insert record into table
set @var = value
end if
get next record
loop
close and deallocate cursor

Hope this helps

Thanks,
Rich

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 10:03:11

[code]-- prepare sample data
declare @t table (Time datetime, Value smallmoney)

insert @t
select '2/6/2007 02:05:00.333', 3.8 union all
select '2/6/2007 02:10:00.333', 3.8 union all
select '2/6/2007 02:15:00.333', 9.0 union all
select '2/6/2007 02:20:00.333', 9.0 union all
select '2/6/2007 02:25:00.333', 9.0 union all
select '2/6/2007 02:30:00.333', 3.8 union all
select '2/6/2007 02:35:00.333', 3.8

-- show the expected output
select d.time,
d.value
from (
select t1.time,
t1.value,
(select max(t2.time) from @t as t2 where t2.time < t1.time) as previous
from @t as t1
) as d
left join @t as t on t.time = d.previous
where t.time is null
or d.value <> t.value[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources