Hi guys,
Hoping to get some help on how to work out the following:
I have 2 tables:
Table A - Consists of where employees are meant to be working (facility) and the start datetime and end datetime.
Table B - When employees are unavailable, start datetime and end datetime.
I want to work out the following:
Using Table B, I would like to know the amount of time unavailable and using Table A, the facility they are meant to be working in and how long they are unavailable for, for that particular facility.
Results table that I am looking for
| Schedule | | | | | |
| Employee | Facility | Start DateTime | End DateTime | Unavailable Time | Unavailable Reason |
| Dave | SE London | 10/01/2024 08:00 | 10/01/2024 12:00 | 2:00 | Sick Leave |
| Dave | S London | 10/01/2024 12:00 | 10/01/2024 16:30 | 3:00 | Sick Leave |
Notice that the way that the unavailable time is calculated is based on the start and end time of employee for the particular Facility. Table A where the Employee is working in SE London, they are meant to be working from 8am-12pm, Table B where it shows they are unavailable between 10am-3pm, I am only summing the time up until 12:00pm.
Where for S London, start time is at 12pm and end time is 4:30pm (table A), the calculation I would imagine should take into account start time of S london 12pm, rather than the start time of the unavailability (which we know is 10am) which is before the start time of this employee for this particular facility.
Hope this makes sense - super happy to explain further as it looks a bit complex for me!
Please see below tables
Thank you so much!!!
Table A:
| Schedule |
| Employee | Facility | Start DateTime | End DateTime |
| Dave | SE London | 10/01/2024 08:00 | 10/01/2024 12:00 |
| Dave | S London | 10/01/2024 12:00 | 10/01/2024 16:30 |
Table B:
| Unavailability | | | |
| Employee | Unavailable Reason | Start DateTime | End DateTime |
| Dave | Sick Leave | 10/01/2024 10:00 | 10/01/2024 15:00 |
