Aggregation Of Overlapping Events Problem

I have a list of 1 to N Shifts Each shift can have N events with their own start and end date I want to Aggregate and salami slice these into a common aggregate event, that changes whenever the shifts overlap & intersect.

So

          <----Time----------------------------------------------->
Shifts -  
1             <----->
2                <-------->
3                     <---------> 
4                                         <------->
Aggregate     <--><--><--><----->         <------->
               1   2   3    4                5

Thus a user can click on one of the aggregate events and see all the staff that are on at that moment in time.

So the sections above would show

1. All staff in shift 1
2. All Staff in shift 1 & 2
3. All Staff in shift 2 & 3
4. All Staff in shift 3
5. All Staff in shift 4

I select all the events I the use a CTE to get all the possible allocation units of time between the first event and the last event.

Then I struggle to take this further to group and aggregate these into single these aggregate blocks.

WITH AllShiftEvents AS (

        SELECT 
            sl.Id AS SwimlaneId, 
            evt.Id AS ShiftEventId, 
            evt.Name As ShiftEventName, 
            evt.StartDate, 
            evt.EndDate,
            evt.ImplicitRisk AS RiskLevel,
            CASE WHEN evt.IsQualityControlReady = 1 THEN evt.ImplicitRisk END AS QcRiskLevel            
        FROM   ShiftEvents se 
        INNER  JOIN Events evt ON evt.Id = se.Id
        INNER  JOIN Swimlanes sl ON evt.SwimlaneId = sl.Id AND sl.WorkUnitId = '82b463bf-50cb-275b-e055-01fd00803469'
),

timeAllocations AS (
        SELECT se.StartDate AS AllocationUnit, 
               se.SwimlaneId,
               se.ShiftEventId,
               se.ShiftEventName,
               se.RiskLevel,
               se.QcRiskLevel
        FROM   AllShiftEvents se
        UNION all 
        SELECT DATEADD(hh,1,AllocationUnit), 
               SwimlaneId, 
               ShiftEventId, 
               ShiftEventName,
               RiskLevel,
               QcRiskLevel
        FROM timeAllocations ta 
        WHERE AllocationUnit < (SELECT s.EndDate FROM AllShiftEvents s where ta.ShiftEventId = s.ShiftEventId)
)

select *
from timeAllocations
order by AllocationUnit
    OPTION (MAXRECURSION 0);

Any ideas?

Answer

You can combine two pieces of information — all times for the shifts and when peoples particular shifts are.

Let me assume you have a single table called shifts. Then you can do:

with t as (
      select v.time,
             lead(v.time) over (order by v.time) as periodend,
             row_number() over (order by v.time) as periodnumber
      from shifts s cross apply
           (values (s.starttime), (s.endtime)) v(time)
      group by v.time
     )
select t.*, s.employee
from t join
     shifts s
     on t.periodstart < s.endtime and
        t.periodend >= s.starttime
order by t.periodstart;

If you have a particular time, it is simpler to do:

select s.*
from shifts s
where s.starttime <= @selectedtime and
      s.endtime > @selectedtime;