1 Reply Latest reply: May 8, 2015 5:37 AM by Gysbert Wassenaar RSS

    Calculating Time Duration between multiple date stamps across two tables

    Thomas Cybucki



      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!