Sergiy (2/13/2013)

---

dwain.c (1/15/2013)

---

I understand you already have a solution but this might be a bit more efficient. The technique is based on an article by Jeff Moden on Grouping Islands of Contiguous Dates[/url] (your periods are like dates).

DECLARE @test-2 TABLE

( VARCHAR(5), Period INT, Data VARCHAR(10))

INSERT INTO @test-2

SELECT 'A',5,'data'

UNION ALL SELECT 'A',6,'data'

UNION ALL SELECT 'A',8,'data'

;WITH CTE AS (

SELECT [Key], FirstPeriod=MIN(Period), LastPeriod=MAX(Period)

FROM (

SELECT , Period, Data

,n=Period-ROW_NUMBER() OVER (PARTITION BY ORDER BY Period)

FROM @test-2) a

GROUP BY [Key], n)

SELECT b., Period, Data, FirstPeriod, LastPeriod

FROM CTE a

INNER JOIN @test-2 b ON a. = b. AND b.Period BETWEEN FirstPeriod AND LastPeriod

Hope this helps!

The script goes nuts if you add another record to the table;

INSERT INTO @test-2

SELECT 'A',5,'data'

UNION ALL SELECT 'A',6,'data'

UNION ALL SELECT 'A',6,'other data'

UNION ALL SELECT 'A',8,'data'