Identify sequence of events based on certain rules I need help on identifying a sequence of events in SQL Server 2008 R2 rows please:

This is the sample data with SQLFiddle:

   ID   EventTime       TypeID             EventNr
    1   06:34:51        1
    2   06:35:51        3
    3   06:36:51        40
    4   06:37:51        10
    5   06:48:51        1
    6   06:49:51        2
    7   06:50:51        4
    8   06:51:51        40
    9   06:52:51        5
    10  06:53:51        10


I need help on identifying Event Nrs please - 
Rules that I must follow:


An event starts start with TypeID 1 (@StartTypeID).
An event ends with TypeID 10 (@EndTypeID).
An event has a different TypeID after @StartTypeID, and before @EndTypeID.
And there must be more than 5 minutes between EventNrs.


This is the explected result for the above example:

 ID EventTime   TypeID  Notes                                               EventNr
1   06:34:51    1       Starts with TypeID @StartID 1     (OK)                     1
2   06:35:51    3       Has different TypeID in sequence before @EndID reached(OK) 1
3   06:36:51    40                                                                 1
4   06:37:51    10      Ends with @EndID 10 + &gt;5min before next Event(OK EventEnd) 1

5   06:48:51    1       Starts with TypeID @StartID 1                              2
6   06:49:51    2       Has different TypeID in sequence before @EndID reached     2
7   06:50:51    4                                                                  2
8   06:51:51    40                                                                 2
9   06:52:51    5       Has different TypeID in sequence before  @EndID reached    2
10  06:53:51    10      Ends with @EndID 10                                        2


SQL for creating the data sample:

declare @FirstTypeID int = 1

declare @LastTypeID int = 10


create table #T (ID int identity , EventTime datetime, TypeID int)
insert into #T(EventTime,TypeID)
values
 (DATEADD(minute,-30,getdate()),1)
 ,(DATEADD(minute,-29,getdate()),3)
 ,(DATEADD(minute,-28,getdate()),40)
 ,(DATEADD(minute,-27,getdate()),10)  
 ,(DATEADD(minute,-16,getdate()),1)
 ,(DATEADD(minute,-15,getdate()),2)
 ,(DATEADD(minute,-14,getdate()),4)
 ,(DATEADD(minute,-13,getdate()),40)
 ,(DATEADD(minute,-12,getdate()),5) 
 ,(DATEADD(minute,-11,getdate()),10)    


select * from #T

drop table #T
This query gives the correct output:

With ord as (
    Select first, last 
        , EventNr = ROW_NUMBER() Over(order by first) 
    From (
        Select first = t1.EventTime, last = t2.EventTime
            , rn = ROW_NUMBER() Over(partition by t1.ID order by t2.eventTime) 
        From #T t1
        Inner Join #T t2 On t2.EventTime &gt; t1.EventTime  and t2.TypeID = @LastTypeID
        Where t1.TypeID = @FirstTypeID
    ) ids
    Where rn = 1
)
Select t.ID, t.EventTime, t.TypeID, o.EventNr From #T t
Inner Join ord o On t.EventTime &gt;= o.first and t.EventTime &lt;= o.last
Order By o.EventNr, t.EventTime


I didn't use Column ID for ordering because it seems to be specific to your samplte.