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 + >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)

select * from #T

drop table #T
  • What if TypeID 1 is immediately followed by TypeID 10? What if TypeID 10 is not immediately followed by TypeID 1? What if there is more than 5 min between events either of both of which are not TypeID 1 or 10? What if there is not more than 5 min between TypeID 10 and the following TypeID 1?
    – Andriy M
    Sep 22 '15 at 7:01
  • hi, if any of those events occur, then the event nr does not increment please. Type1 immediately Type10 (no event in between -stay on current event nr). Type 10 not immediately followed by Type1 (stay on current event nr) . Not more than 5min between 10 and 1 (stay on current event Nr) If I can get an idea of what you can recommend then I can sort out the dates based on your recommended approach but I have no clue where to start please Sep 22 '15 at 7:04
  • I would start with enumerating the rows. That would allow me to join the dataset to itself matching each row with its neighbours (so it would probably be two self-joins) to look at their properties. In fact, that's what I'm working on at the moment. (But I thought I'd let you know in case you want to try something on your own.)
    – Andriy M
    Sep 22 '15 at 7:49
  • Thanks Andriy, I included auto identity for simplicity, but am using row_number for enumeration. are you referring to partitioning, which is effectively what Im attempting - to partition by groups? Sep 22 '15 at 8:01
  • No, I meant global enumeration. At least that's what seemed to me necessary as the first step. Your IDs do indeed look like row numbers, I just wasn't sure I could rely on that and thanks for clarifying that I was right to doubt. :) As for enumeration in partitions, that might be needed as well, I'm not certain. You are perfectly free to explore that direction, of course.
    – Andriy M
    Sep 22 '15 at 8:17

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 > 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 >= o.first and t.EventTime <= 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.

  • I know this is an old answer and would like to know if it could be improved by using windowing functions FIRST_VALUE / LAST_VALUE? Better yet read firstTypeID and lastTypeID pairs from a table. Thanks in advance. Sep 15 '20 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.