Calculating Time Duration between multiple date stamps across two tables

tcybucki — Thu, 07 May 2015 23:51:14 GMT

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.

Any help would be greatly appreciated! Thank you!

Re: Calculating Time Duration between multiple date stamps across two tables

Gysbert_Wassenaar — Fri, 08 May 2015 09:37:39 GMT

This problem is discussed in IntervalMatch and Slowly Changing Dimensions. See page 19 of the pdf file attached to that document.

topic Calculating Time Duration between multiple date stamps across two tables in App Development

Calculating Time Duration between multiple date stamps across two tables

Re: Calculating Time Duration between multiple date stamps across two tables