Hi

This should get the result that you want, with a caveat. It assumes that you do not have gaps in your date ranges.

WITH cteGroup AS (

SELECT ID, VAL, STARTDATE, ENDDATE,

dateGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY STARTDATE) -

ROW_NUMBER() OVER (PARTITION BY ID, VAL ORDER BY STARTDATE)

FROM #DATA

)

SELECT ID, VAL, MIN(STARTDATE) STARTDATE, MAX(ENDDATE) ENDDATE

FROM cteGroup

GROUP BY ID, VAL, dateGroup

ORDER BY ID, MIN(STARTDATE);

Jeff Moden has done a excellent article[/url] on grouping islands of contiguous dates that may also help you.

Edit: Fixed link