First of all, and a good thing for you because I suck at them, I don't think this is a gaps and islands problem. It looks more like a cumulative totals problem, so I propose it be handled with a quirky update. You'll need to add a helper column to your table and reverse the primary key/index clustering.

CREATE TABLE #tempTable (EvDate date NOT NULL, EID int NOT NULL

,helpergroupno INT,

CONSTRAINT PKtemp PRIMARY KEY NONCLUSTERED (EvDate ASC, EID ASC))

CREATE UNIQUE CLUSTERED INDEX IXtemp ON #tempTable (EvDate DESC )

Now add in your setup data and then run this:

DECLARE @tempstr VARCHAR(100) = ''

,@tempno INT = 1

UPDATE #tempTable

SET @tempstr = CASE

WHEN CHARINDEX('[' + CAST(EID AS VARCHAR) + ']', @tempstr) = 0

THEN @tempstr + '[' + CAST(EID AS VARCHAR) + ']'

ELSE '[' + CAST(EID AS VARCHAR) + ']' END

,@tempno = helpergroupno = CASE

WHEN @tempstr = '[' + CAST(EID AS VARCHAR) + ']'

THEN @tempno + 1

ELSE @tempno END

OPTION(MAXDOP 1)

SELECT startdate=MIN(EvDate), enddate=MAX(EvDate), [Count of EIDs]=COUNT(*)

FROM #tempTable

GROUP BY helpergroupno

DROP TABLE #tempTable

Results are:

startdateenddateCount of EIDs

2012-02-272012-04-026

2012-04-092012-05-288

2012-05-302012-05-301

2012-06-042012-07-167

2012-07-232012-08-205

2012-08-272012-08-271

Please advise if this is correct. 😀

Edit: Corrected the code above to handle EID > 9 and added this explanation:

I realized that my suggestion that this is a cumulative sums problem may be a bit enigmatic, so let me say that it is except that instead of accumulating totals you're accumulating events.

If you're unclear as to how it works you can do this:

1. Add a second (VARCHAR(100)) helper column and dump the current value of @tempstr into it as I have done with @tempno in the row that follows.

2. SELECT * from the table without grouping.