How can I count continuous time frames
My data looks like this:
Id| Em_Name|Em_Reg_Date
--------------------------------
1 | John |2010-03-30 00:00:00
1 | John |2010-03-31 00:00:00
2 | Marc |2010-10-26 00:00:00
2 | Marc |2010-10-27 00:00:00
2 | Marc |2010-10-28 00:00:00
2 | Marc |2010-10-29 00:00:00
2 | Marc |2010-12-16 00:00:00
2 | Marc |2010-12-17 00:00:00
2 | Marc |2010-12-20 00:00:00
2 | Marc |2010-12-21 00:00:00
2 | Marc |2010-12-22 00:00:00
3 | Paul |2010-02-25 00:00:00
3 | Paul |2010-02-26 00:00:00
3 | Paul |2010-12-13 00:00:00
3 | Paul |2010-12-14 00:00:00
3 | Paul |2010-12-15 00:00:00
--------------------------------
A time frame is a continuous period of time.
e.g. Paul has following two (2) time frames
FRAME 1 FROM 2010-02-25 00:00:00 to 2010-02-26 00:00:00
FRAME 2 FROM 2010-12-13 00:00:00 to 2010-12-15 00:00:00
So, the result should be like this
1 John 1
2 Marc 3
3 Paul 2
The question is: I need to count time frames for each Employee.
The problem here lies in the fact that I need to isolate the continues time frames in order to count them. I've even tried a declare cursor (works but I've to store the data in a temp table) And I want this to be in a "simple" sql statement Using max to find a start date works for only one frame. You can not find the second/third frame with max.
Is there anyone with fresh new ideas?
SQL Server 2005+
Oracle, DB2 also support Row_Number(), but you will need some variation to calculating startdate