Smoothing / Merging Timeframes

Question

I have a Service table with millions of rows. Each line corresponds to the service provided by the staff on a given date and time interval (each line has a unique identifier). There are times when employees can provide services over an overlapping time frame. I need to write a query that combines overlapping time intervals and returns data in the format shown below.

I tried to group the StaffID and Date fields and get the Min of BeginTime and Max of EndTime, but this does not take into account non-overlapping time frames. How can i do this? Again, the table contains several million records, so the CTE recursive approach may have performance issues. Thanks in advance.

Service table

ID    StaffID  Date        BeginTime EndTime
1     101      2014-01-01  08:00     09:00
2     101      2014-01-01  08:30     09:30
3     101      2014-01-01  18:00     20:30
4     101      2014-01-01  19:00     21:00

Exit

StaffID Date        BeginTime EndTime
101     2014-01-01  08:00     09:30
101     2014-01-01  18:00     21:00

, . http://sqlfiddle.com/#!6/bfbdc/3

(06: 00-08: 45), (06: 00-08: 30 06: 00-08: 45)

Answer 1

CTE, , , . 1 2, 2 3 .. CTE .. , .

CTE , , , ( , ). , , , - , . , .

SQLFiddle , .

ServiceID, , BeginTime.

;with flat as
(
 select StaffID, ServiceDate, BeginTime, EndTime, BeginTime as groupid 
 from services S1
 where not exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime <= S1.BeginTime and S2.EndTime <> S1.EndTime
 and S2.EndTime > S1.BeginTime)

  union all

  select StaffID, ServiceDate, BeginTime, EndTime, BeginTime as groupid 
  from services S1
 where exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime = S1.BeginTime and S2.EndTime > S1.EndTime)
   and not exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime < S1.BeginTime
 and S2.EndTime > S1.BeginTime)

 union all

 select S.StaffID, S.ServiceDate, S.BeginTime, S.EndTime, flat.groupid 
 from flat
 inner join services S 
 on flat.StaffID = S.StaffID
 and flat.ServiceDate = S.ServiceDate
 and flat.EndTime > S.BeginTime
 and flat.BeginTime < S.BeginTime and flat.EndTime < S.EndTime
)

select StaffID, ServiceDate, MIN(BeginTime) as begintime, MAX(EndTime) as endtime 
from flat
group by StaffID, ServiceDate, groupid
order by StaffID, ServiceDate, begintime, endtime