Third running streak
declare @t table (Id int, dt datetime, value int)
set dateformat 'dmy'
insert into @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8
SELECT Id,
MIN(dt) AS Startdt,
MAX(dt) AS Enddt,
MIN(value) AS Value
FROM (
SELECT Id,
dt,
value,
ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID,
ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID,
ROW_NUMBER() OVER (ORDER BY dt) AS colID
FROM @t
) AS d
GROUP BY Id,
grpID - recID,
grpID - colID
ORDER BY id,
MIN(dt)