Finding longest sequence of events within a table

Problem is to find the longest period of an event in an event log. Consider database for international cricket teams which contains the log of their performance over the years. We need to find longest winning streak for all teams using T-SQL.

We have a table MatchResults with following information Team Name, Match date, Result( W for Win, L for Lost)

ID	Team	Result	Date
1	India	W	12/10/2013
2	England	L	12/15/2013
3	India	L	12/18/2013
4	India	W	12/30/2013
5	England	W	1/7/2014
6	India	W	1/14/2014
7	Australia	L	1/27/2014
8	India	W	2/12/2014
9	England	L	2/18/2014
10	India	W	2/25/2014
11	England	W	3/9/2014
12	England	W	3/11/2014
13	England	W	3/13/2014
14	Australia	W	3/21/2014
15	India	W	3/24/2014

We need to find the longest winning streak for each team form above data. Following is expected result.

Team	Wins
Australia	1
England	3
India	5

T-SQL:

;WITH R AS

(        SELECT Date,

                     Team,

                     Result ,

                     ROW_NUMBER() OVER (PARTITION BY Team ORDER BY Date) AS Seq

          FROM dbo.MatchResults

),

T AS

(        SELECT  Date,

                      Team,

                      RESULT,

                      Seq,

                      0 AS rec,

                      CASE WHEN RESULT = 'L' THEN 0 ELSE 1 END AS wincount

          FROM R

          WHERE Seq = 1

  

          UNION ALL

  

          SELECT R.Date,

                      R.Team,

                      R.RESULT,

                      r.seq ,

                      t.rec + 1 AS rec,

                   CASE  WHEN r.RESULT = 'L' THEN 0  ELSE t.wincount + 1  END AS wincount

          FROM R

         INNER JOIN T ON (T.Seq = R.Seq -1 AND t.Team = R.Team)

)

SELECT Team,

       MAX(wincount) Wins

FROM T

GROUP BY Team