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'
_____________
Code for TallyGenerator