1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
May 2017
Posts
1
Rep Power
0

#### Overlapping time

Hello
I have 2 tables with workers and supervisor data I need the output as any aggregation data of hours spent by
supervisor with worker. There will be overlap of time but we need to consider only the time he spent not overlap as
additional time spent. This needs be done for all the supervisors in the table dynamically.

Worker Working hours with supervisor

Supervisor | Worker | Start Time | End time
----------------------------------------------------------------------------------------------------------------
Z8956 | Mike | 10:33:40 AM | 4:34:23 PM

Z8956 | Robin | 2:52:03 PM | 3:29:22 PM

Z8956 | Riche | 3:26:55 PM | 4:34:23 PM

D6635 | Sam | 12:38:46 PM | 1:00:27 PM

D6635 | Jeff | 8:38:23 AM | 9:51:26 AM

D6635 | Joe | 7:56:54 AM | 8:29:31 AM

supervisor working hours

Supervisor | Start Time | End time
--------------------------------------------------------------------------------------------------------------------------
Z8956 | 10:00:00am | 5:00:00 PM

D6635 | 7:00:00am | 2:00:00 PM

Output should be as below:
This should not consider the overlap time

Supervisor | Total Hours Spent with worker | Total Hours supervisor is free
----------------------------------------------------------------------------------------
Z8956 | 180mins | 200mins
D6635 | 200mins | 500mins

Any help greatly appreciated
2. i don't have an answer for you... that's quite an interesting and difficult problem

let me use this overlap diagram --

Code:
```              09:00      17:00
|          |
|          |
1.  S------E    |          |
|          |
|          |
2.          S------E       |
|          |
|          |
3.              | S------E |
|          |
|          |
4.          S----------------------E
|          |
|          |
5.              |      S------E
|          |
|          |
6.              |          |    S------E

S=start_time   E=end_time```
to look at the overlaps between 09:00 and 17:00, you want to eliminate cases 1 and 6

this is the SQL you need for that

Code:
```WHERE end_time   >= '09:00'   /* eliminates case 1 */
AND start_time <= '17:00'   /* eliminates case 6 */```
however, now the fun begins

if i understand your requirements, because of case 4, "Total Hours Spent with worker" will by 8.0, while "Total Hours supervisor is free" will be zero, regardless of cases 2, 3, and 5

if case 4 were excluded, then you still have 100% time with worker because they overlap

this is a tricky problem indeed
3. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,953
Rep Power
382
Can a supervisor have more than one session with a given worker?

```Z8956 | Z8956 | 00:00:00 | 10:00:00