Detecting value changes and grouping by them

Hello,

I was wondering if anyone can help me with a problem I have. I have a
program (beyond my control) that constantly posts to a table the current
value of a set of parameters identified by name. I need to query it and
return for a given parameter and start\end times which values it had
between those times and the minimum and maximum times for each value.

To clarify, suppose I have the following table:

fName fTime fValue
----- ----- ------
Par2 1/1/2006 00:00 9
Par2 1/1/2006 00:01 9
Par2 1/1/2006 00:02 5
Par2 1/1/2006 00:03 5
Par2 1/1/2006 00:04 5
Par2 1/1/2006 00:05 3
Par1 1/1/2006 00:00 1
Par1 1/1/2006 00:01 1
Par1 1/1/2006 00:02 1
Par1 1/1/2006 00:03 1
Par1 1/1/2006 00:04 2
Par1 1/1/2006 00:05 2
Par1 1/1/2006 00:06 2
Par1 1/1/2006 00:07 2
Par1 1/1/2006 00:08 3
Par1 1/1/2006 00:09 3
Par1 1/1/2006 00:10 1
Par1 1/1/2006 00:11 2
Par1 1/1/2006 00:12 2
Par1 1/1/2006 00:13 4
Par1 1/1/2006 00:14 4
Par1 1/1/2006 00:15 4

For the parameter "Par1" and the time range "1/1/2006 00:05" to
"1/1/2006 00:14" I run the following query:

SELECT Min(fTime) AS FromTime, Max(fTime) AS ToTime, fValue AS Lbl
FROM tParams
WHERE (fName = 'Par1')
GROUP BY fName, fValue
HAVING (Min(fTime) <= '2006-1-1 00:14')
AND (Max(fTime) >= '2006-1-1 00:05')

And get the following result:

FromTime ToTime Lbl
-------- ------ ---
1/1/2006 00:04 1/1/2006 00:12 2
1/1/2006 00:08 1/1/2006 00:09 3
1/1/2006 00:00 1/1/2006 00:10 1
1/1/2006 00:13 1/1/2006 00:15 4

What I'm actually trying to get is:

FromTime ToTime Lbl
-------- ------ ---
1/1/2006 00:04 1/1/2006 00:07 2
1/1/2006 00:08 1/1/2006 00:09 3
1/1/2006 00:10 1/1/2006 00:10 1
1/1/2006 00:11 1/1/2006 00:12 2
1/1/2006 00:13 1/1/2006 00:15 4

That is, I want to detect all consecutive sequences of the same fValue
that intersect with the dates I requested (for the given fName) and
output the start-time and end-time of each sequence. Is there any way to
do so without resorting to client-side programming?

Thanks

Using SQL Server 2005, you can do this (uses a difference of ranks)

set nocount on

create table tParams(fName char(4),fTime datetime,fValue int)
insert into tParams(fName ,fTime ,fValue)
select 'Par2' , '1/1/2006 00:00' , 9 union all
select 'Par2' , '1/1/2006 00:01' , 9 union all
select 'Par2' , '1/1/2006 00:02' , 5 union all
select 'Par2' , '1/1/2006 00:03' , 5 union all
select 'Par2' , '1/1/2006 00:04' , 5 union all
select 'Par2' , '1/1/2006 00:05' , 3 union all
select 'Par1' , '1/1/2006 00:00' , 1 union all
select 'Par1' , '1/1/2006 00:01' , 1 union all
select 'Par1' , '1/1/2006 00:02' , 1 union all
select 'Par1' , '1/1/2006 00:03' , 1 union all
select 'Par1' , '1/1/2006 00:04' , 2 union all
select 'Par1' , '1/1/2006 00:05' , 2 union all
select 'Par1' , '1/1/2006 00:06' , 2 union all
select 'Par1' , '1/1/2006 00:07' , 2 union all
select 'Par1' , '1/1/2006 00:08' , 3 union all
select 'Par1' , '1/1/2006 00:09' , 3 union all
select 'Par1' , '1/1/2006 00:10' , 1 union all
select 'Par1' , '1/1/2006 00:11' , 2 union all
select 'Par1' , '1/1/2006 00:12' , 2 union all
select 'Par1' , '1/1/2006 00:13' , 4 union all
select 'Par1' , '1/1/2006 00:14' , 4 union all
select 'Par1' , '1/1/2006 00:15' , 4 ;

with cte(fName,fTime,fValue,rn)
as (select fName,
fTime,
fValue,
rank() over(partition by fName order by fTime)-
rank() over(partition by fName,fValue order by fTime)
from tParams)
select min(fTime) as FromTime,
max(fTime) as ToTime,
fValue as Lbl
from cte
where fName = 'Par1'
group by fName,fValue,Rn
having (min(fTime) <= '2006-1-1 00:14')
and (max(fTime) >= '2006-1-1 00:05')
order by FromTime


Note that this is also possible with SQL Server 2000, but is a bit more
complex