I have a set of database tables in Microsoft SQL Server "in the example flattened to keep this example easy" that is a bunch of time clock records for employees that looks like this.

Call this table tctable

I have a set of database tables in Microsoft SQL Server "in the example flattened to keep this example easy" that is a bunch of time clock records for employees that looks like this.

Call this table tctable

null means they haven't clocked out.

Imagine the following statuses,

Partial Functional PF - If you have a manager and one other employee (including another manager) You are also PF if you have a cashier and a warehouse person

Fully Functional FF - If you have a manager, a cashier, and a warehouse person

Not Functional NF - If you don't meet either other criteria.

What we are looking to do is examine when a certain number of people are in during overlapping times, so ideally we would want to see from 7:00 to 8:00 we only had a Manager so NF. The resulting table I would like to get would be

I have tried joining the table to itself where the start date is greater then the start date and end date is less then the end date but I get strange results I think because times can be nested and it can only ever return 2 overlapping then, so 3 people at the same time gets lost. Any way to write this to solve the problem no matter how many overlapping times. We could have a dozen employees at the same time. Below is the query that gets back some overlaps but has lots of extra records and doesn't really get close (as it can only really handle single overlaps) to the result where I can compare tc1.employee_job to tc2.employee_job to create the status records.

SELECT tc1.employee_job, tc2.employee_job, tc1.start_date_time, tc2.end_date_time
FROM tctable tc1
LEFT OUTER JOIN
tctable tc2 on ((tc1.start_date_time >= tc2.start_date_time 
AND tc1.start_date_time <= ISNULL(tc2.end_date_time,GETDATE()))
OR (ISNULL(tc1.end_date_time,GETDATE()) <= ISNULL(tc2.end_datetime,GETDATE()) 
AND ISNULL(tc1.end_date_time, GETDATE()) >= tc2.start_date_time)) AND tc1.key <> tc2.key

• sql
• sql-server
• datetime
• sql-server-2008
• date-range

• 1 A simple logic to detect overlaps is x.start < y.end and y.start < x.end: you do not need to compare start to start and end to end. – Guillaume Outters Commented Mar 20 at 16:01
• The employee id and employee name columns can be removed from your tctable example as they are not required to generate the results you want. They can be completely ignored. – Bart McEndree Commented Mar 20 at 18:02

2 Answers 2

To stay anized, you'll split your task in steps:

• first determine the moments when there is a change in the staff
  (those will be the moments when we'll need to recompute the staffing; between those moments it stays stable)
• then split each employee presence in slices aligned on those moments
  (thus Anne's morning is split in 3: from 8 to 11 with Bob, from 11 to 12 with Frank, from 12 to 13 with Tim)
• then group by slice, to know which roles (and how many for managers) are present during this slice
• and determine functional status from the roles present

This naturally maps to a CTE:

with
    -- Determine where there is change in the team (either an arrival or a departure).
    changes as (select start_date_time t from tctable union select end_date_time from tctable),
    -- Generate our timeline as the succession of spans between two changes.
    timeline as
    (
        select
            row_number() over (order by case when t is null then 1 else 0 end, t) id,
            t tfrom,
            lead(t) over (order by case when t is null then 1 else 0 end, t) tto
        from changes
        where t is not null
    ),
    -- Now match each employee presence with the span (or spans) it overlaps.
    -- Then group by span ID, concatenating present roles.
    presence as
    (
        select
            t.id,
            concat
            (
                case when sum(case when employee_job = 'Cashier' then 1 end) > 0 then 'C' else '' end,
                case when sum(case when employee_job = 'Manager' then 1 end) >= 2 then 'MM' when sum(case when employee_job = 'Manager' then 1 end) >= 1 then 'M' else '' end,
                case when sum(case when employee_job = 'Warehouse' then 1 end) > 0 then 'W' else '' end
            ) roles,
            count(distinct employee_job) n_roles
        from timeline t join tctable e
        -- /!\ Overlap is computed by crossing starts and ends: "span1.start < span2.end && span2.start < span1.end":
        --     [s1----------------s1]
        --              [s2-----------------s2]
        --     [s1------[s2+++++++s1]-------s2]
        on (e.start_date_time < t.tto or t.tto is null)
        and (e.end_date_time > t.tfrom or e.end_date_time is null)
        group by t.id
     )
 -- Now check presence against known patterns.
select
    case
        when n_roles = 3 then 'FF'
        when n_roles = 2 or roles like '%MM%' then 'PF'
        else 'NF'
        -- N.B.: we have no status (and no output line) for "0 employee present"!
    end functional_status,
    t.tfrom start_date_time, t.tto end_date_time
from timeline t join presence p on p.id = t.id
order by t.id;

(notice how the slices are ungrouped, for example 8 - 11 and 11 - 12 stay separated, although both are PF; this shows the Bob-by-Frank replacement)

Note that I couldn't find an SQL Server 2008 so this may need adaptations.
The oldest I could find is a 2014, on which I ran a fiddle.

I will suggest "old style" example - for SQL Server 2008. Not used windows functions, concat and so on.

1. Cte EmployeeInOut - timetable for events - Emplyeer start or end job. Null value for datetime substituted by cast('9999-12-31T23:59:59.997' as datetime) - max value for datetime.

2. workersInOut - we consolidate events with the same datetime and count changes for each employee_job. For example, if 1 Manager started work and another is ended work, chngManager is 0 - there is no effect on the status.

3. cumulativeTotals - calculate cumulative count for each employee_job. Instead of window function, we use self join and group by. Join condition is rows between unbounded preceding and 1 preceding - t2.event_time<t1.event_time.
   Also we add values for current rows.
   We may use condition between unbounded preceding and current, if needed. Then condition is t2.event_time<=t1.event_time, and we don't add current row value.

4. statuses - calculate status for every row with your conditions.

5. chngStatus and main query - consolidate adjacent rows with the same status values

with EmployeeInOut as(
   select id,employee_id,employee_name,employee_job,start_date_time event_time, +1 dir
   from tctable
 union all
   select id,employee_id,employee_name,employee_job,coalesce(end_date_time,cast('9999-12-31T23:59:59.997' as datetime)), -1 dir
   from tctable
)
,workersInOut as(
select event_time
  ,sum(case when employee_job='Manager' then dir else 0 end)chngManager
  ,sum(case when employee_job='Cashier' then dir else 0 end)chngCashier
  ,sum(case when employee_job='Warehouse' then dir else 0 end)chngWarehouse
from employeeInOut
group by event_time
)
,cumulativeTotals as(
  select t1.event_time
    ,min(t1.chngManager)+coalesce(sum(t2.chngManager),0) cntManager
    ,min(t1.chngCashier)+coalesce(sum(t2.chngCashier),0) cntCashier
    ,min(t1.chngWarehouse)+coalesce(sum(t2.chngWarehouse),0) cntWarehouse
  from workersInOut t1
  left join workersInOut t2 on t2.event_time<t1.event_time
  group by t1.event_time
)
,statuses as(
select event_time
    ,case when cntManager>0 and cntCashier>0 and cntWarehouse>0 then 'FF'
          when  (cntManager>0 and (cntCashier>0 or cntWarehouse>0 or cntManager>1) )
             or (cntCashier>0 and cntWarehouse>0)
       then 'PF'
     else 'NF'
     end status
from  cumulativeTotals 
)
,chngStatus as(
select * 
  ,(select min(event_time) from statuses t2 where t2.status<>t.status and t2.event_time>t.event_time) next_time
from statuses t
)
select min(status) status,min(event_time) start_time,nullif(next_time,cast('9999-12-31T23:59:59.997' as datetime)) as end_time 
from chngStatus
where next_time is not null
group by next_time
;

fiddle