Schedule coverage %

[qh8519a]

My overall goal is to find out the % of schedule overlap a team leader has
with their team. There is a team leader table for each DOW that has [Team
Leader], [Start], [Stop]. There is a team member table for each DOW that has
[Team Member Name],[Team Leader Name], [Start], and [Stop]. Each team leader
has several team members and I would like to the best way to find out the
percentage of overlap between the team leader and team members.

For example, Leader A has 10 people on their team. The leader's schedule is
Mon-Fri 9:00-17:00. 9 of the team members have the same schedule as the
leader, but one team member works 18:00-02:00 Mon-Fri. The team leader would
have a team coverage % of 90% because there is a 90% schedule overlap between
the team leader and team members.

What would be the best way to go about reaching the overlap%? I am using
Access 2003.

The current environment uses an excel spread sheet with several static
values for each 15 minute interval of each day (schedules are for a 24 hour
environment). If statements are used to determine whether each 15 minute
interval of a team member is covered by the team leader's schedule.

 

[Michel Walsh]

The sequence <start, end> is disjoint from the sequence <begin, finish> if,
and only if:

start > finish OR end < begin



The two sequences overlap (in part or in full) in the negative (De Morgan's
law):

start <= finish AND end >= begin


So:
(assuming an employee is listed just once in tableName).

SELECT COUNT(*)
FROM tableName
WHERE [start] <= finish AND [end] >= begin



count the number of employee having their schedules in the interval begin,
finish have some overlap with the parameter defining a sequence start,
end.



SELECT 100 * COUNT(*) / (SELECT COUNT(*) FROM tableName)
FROM tableName
WHERE [start] <= finish AND [end] >= begin


gives the same, but as a percentage over all employee.

Note that the = part is potentially a problem, so you may have to remove
them (ie, using strict inequality), since there is no overall at all even if
an employee arrives at the exact moment the supervisor leaves.

Vanderghast, Access MVP