Forum Discussion

threw000's avatar
threw000
Copper Contributor
Jan 10, 2024

Date Time - matching criteria and summing time

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     
EmployeeFacilityStart DateTimeEnd DateTimeUnavailable TimeUnavailable Reason
DaveSE London10/01/2024 08:0010/01/2024 12:002:00Sick Leave
DaveS London10/01/2024 12:0010/01/2024 16:303:00Sick 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
EmployeeFacilityStart DateTimeEnd DateTime
DaveSE London10/01/2024 08:0010/01/2024 12:00
DaveS London10/01/2024 12:0010/01/2024 16:30

 

Table B:

Unavailability   
EmployeeUnavailable ReasonStart DateTimeEnd DateTime
DaveSick Leave10/01/2024 10:0010/01/2024 15:00

 

6 Replies

Resources