Here is a set based solution. I left out the names (since you didn't supply test data), but I'm sure you can add that into the joins with no problem.declare @d table (date datetime primary key clustered )declare @ds table (date datetime primary key clustered )declare @de table (date datetime primary key clustered )print 'Load test data'insert into @dselect '2006-09-01' union allselect '2006-09-05' union allselect '2006-09-06' union allselect '2006-09-07' union allselect '2006-09-08' union allselect '2006-09-11' union allselect '2006-09-12' union allselect '2006-09-13' union allselect '2006-09-14' union allselect '2006-09-15'order by 1print 'Find Start of each series'insert into @dsselect a.datefrom @d a left join @d b on a.Date = dateadd(day,1,b.Date)where b.Date is nullprint 'Find End of each series'insert into @deselect a.datefrom @d a left join @d b on a.Date = dateadd(day,-1,b.Date)where b.Date is nullprint 'Find Longest and Shortest Series'select [Longest Series] = max(datediff(dd,c.StartDate,EndDate))+1 , [Shortest Series] = min(datediff(dd,c.StartDate,EndDate))+1from ( -- Match Start of series to End oi series select StartDate = a.date, EndDate = min(b.date) from @ds a join @de b on a.Date <= b.date group by a.Date) c
Results:Load test data(10 row(s) affected)Find Start of each series(3 row(s) affected)Find End of each series(3 row(s) affected)Find Longest and Shortest SeriesLongest Series Shortest Series -------------- --------------- 5 1(1 row(s) affected)
CODO ERGO SUM