Calculating Time Duration between multiple date stamps across two tables
Hi,
I am trying to analyze how many hours we lose to various types of employee absence. I have two tables that I have connected by Employee IDs: a Schedule Table and a Leave Table.
The schedule table looks like the following:
Employee ID Shift Start Shift End Location
1 4/14/2015 00:00 4/14/2015 08:00 A
2 4/14/2015 10:00 4/14/2015 18:00 A
3 4/14/2015 16:00 4/15/2015 00:00 B
and so on...we are a 24 hour operation with hundreds of employees, and I have schedule records for each day in the past couple years.
The leave table looks like the following:
Employee ID Leave Start Leave End Leave Type
1 4/14/2015 00:00 4/14/2015 08:00 Sick
1 4/18/2015 00:00 4/23/2015 23:59 WCB
2 4/22/2015 12:00 4/29/2015 18:00 Long Term Medical Leave
and so on...
Employee schedules are fairly consistent over a few weeks, but they drastically change every 4-5 months. The leave table does not follow the same time convention as the schedule table. Generally, long term leave is coded as 00:00 to 23:59 for a date range, and short term (less than 1 day) leave is coded to match an employee's shift. But it can vary wildly given who coded the leave into our scheduling system.
I want to create a table where I can see by day (and by week eventually) how many hours we lose to the various Leave Types. I am having trouble connecting all my timestamps and then calculating hours lost. I was attempting to copy another thread idea where I load each timestamp column into a CommonDate column, but I ended up creating many synthetic keys. I have created Master Calendar's in other apps, but this particular app is the first time I have had four date columns.
I do want to be able to see which employees are on every type of leave, but my main goal is to see how many hours each day/week we lose to various leaves so we can schedule accordingly.
