Partitioning consecutive events

[ZenRaven]

Hi all,

I have a table of operator events. OperatorID and EventTime are the important columns. What I want is to return all of the events but to identify groups of consecutive events by operator without cursors or looping.

example (simplified):

OperatorID EventTime
1 10:00
1 11:00
1 12:00
2 14:00
2 15:00
1 16:00
1 17:00
3 18:00

should return

OperatorID EventTime GroupNumber
1 10:00 1
1 11:00 1
1 12:00 1
2 14:00 2
2 15:00 2
1 16:00 3
1 17:00 3
3 18:00 4

I've been messing around with

SELECT OperatorID, EventTime,
ROW_NUMBER() OVER(PARTITION BY OperatorID ORDER BY EventTime)
from OperatorEvents

which correctly partitions the set but only gives row numbers within each parition. I need something that identifies the partition itself.




--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[markros]

;with cte as (select OperatorID, row_number() over (order by OperatorID) as GroupNumber from (select OperatorID from OperatorEvents group by OperatorID) T)

select OE.*, cte.GroupNumber from OperatorEvents OE inner join cte on OP.OperatorID = cte.OperatorID

 

[ZenRaven]

Thanks for the feedback but that doesn't give the required results. With the example I stated it would return all OperatorID 1 records as GroupNumber 1 when in fact there would be 2 different GroupNumbers for OperatorID 1 since OperatorID 2 has a group of events in between.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[markros]

I understand the problem better now and could not come up to a simple solution from the top of my head - don't have much time to play in SSMS right now.

 

[markros]

I think we can get it done it two steps - select with place holder and then use an update command. Check this thread

http://forums.asp.net/t/1408081.aspx

I think we should do something along the same lines in this case.