Getting duration in minutes outside predetermined time range
I have two qvds. One contains employee schedule information (Start, End and everthing in between). The other contains the actual events that occured for employees (start, stops, event code, duration and others). Although I have the when, the what and for how long each event occured, I cant figure out a way to calculate the amount of minutes a person was and was not in a scheduled event. I am attempting to calculate schedule adherence.
In sounds simple in thought: For every minute outside of a scheduled event add one minute out of adherence and vice versa but I am stomped. The following are some of the fields I have available to me.
ActivityID, EmployeeID, Date ,ahEndTime, ActualDayEndTime, ActualDayDuration, ActualDayStartTime, activityEndTime, activityDuration, activityStartTime (in minutes from midnight & formated),activityName.
These are available for both tables and are stored seperately in qvds.
I thought I could figure this out by calculating the start/stop times which are in bewteen the events but I always came back to the same problem: How to count the minutes. Any assistance would be much appriciated. Thank you.