sql - Group rows into sequences using a sliding window on a DateTime column -

I have a table that stores the timestamps events. I use events in Timestamp columns using the 5-minute sliding window I want to add to the 'sequence' in the group, and 'sequence id' (any ID that can distinguish the sequences) and in the second order, the order is written in sequence.

Input - Event table:

  + ---- + ------- + ----------- + | ID | Name | Timestamp | + ---- + ------- + ----------- + | 1 | Test | 00:00:00 | | 2 | Test | 00:06:00 | | 3 | Test | 00:10:00 | | 4 | Test | 00:14:00 | + ---- + ------- + ----------- +  

desired output - Order table. Here is the identity of the initial event of SEQId, but it is nothing to identify the sequence specific.

  + --------- + ------- + ---------- + | EventID | SeqId | SeqOrder | + --------- + ------- + ---------- + | 1 | 1 | 1 | | 2 | 2 | 1 | | 3 | 2 | 2 | | 4 | 2 | 3 | + --------- + ------- + ---------- +  

What would be the best way to do this? This is MSSQL 2008, I use SSAS and SSIS if they make things easy.

  create table # input (id INT, name VARCHAR (20), timestamp time) # Insert the input value (1, 'test', '00: 00: 00'), (2, 'test', '00: 06: 00'), (3, 'test', '00:10:00' ), (4, 'exam', '00:14:00') SELECT * FROM #Input; As with CTE - more than 5min of Eid (Selection seems to be with a difference of CTE *, case - select a sequential number (*, more than ROW_NUMBER (), on order (ID based on order) In the form of #INPUT, add it as cte2. When the DateDiff (MI, cte_1.Time_Stamp, cte.Time_stamp) of <5 then in the form of GrpType as 0 and the end of the CTE Left External from cte.sort on cte_1 = Cte_1.sort +1), as included in CTE3 CTE - A SeqId Allocated (Selection Grout from SeqId Where GrpType = 1), cte4 by More than Selection GrpType, TIME_STAMP, ROW_NUMBER () As -. SeqId (select cte3 per TIME_STAMP series *, TS_to as cte_2.Time_stamp cte3 left outer join cte3 cte3.SeqId = cte_2.SeqId-1 on cte_2) - Join cte4 interiors from SeqOrder # as last query Select t.Id, cte4.SeqId, ROW_NUMBER (greater than) by t.time_stamp part by cte4.SeqId order). Time_stamp & gt; = Input T and (T. timestast & lt; ct4.ts_to or cte4.TS_to IS tap) on cte4.Time_stamp;  

This code is a bit more complicated but it gives the expected output (which does not solve Gordon Linoff ...) and it's a little bit faster.