Very inefficient but gives correct results

SELECT Students AS Name,

CASE WHEN COUNT(*) > 1 THEN 'Y' ELSE 'N' END AS IsASequence,

MIN(CAST(Subjects AS INT)) AS SeqStart,

MAX(CAST(Subjects AS INT)) AS SeqEnd

FROM (

SELECT t1.Students,

t1.Subjects,

CAST(t1.Subjects AS INT) - (SELECT COUNT(*) FROM #SeqStudentSubj t2

WHERE t2.Students=t1.Students AND CAST(t2.Subjects AS INT) <= CAST(t1.Subjects AS INT)) AS rn

FROM #SeqStudentSubj t1) x

GROUP BY Students,rn

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537