Calculate value from previous calendar day

[madman411]

Hi all

I've written a small database that calculates crew hours in the production and entertainment industry. I've been asked to "flag" employees automatically that have turnaround violations (i.e. they weren't given enough time off between finishing the previous day and starting on the current day as stipulated by their particular union contract.) The goal being to list the names each day so the department heads have an idea who are accruing these rest period violations.

I currently have a "day" table where the work date is entered and each employee working is added to a portal and their hours are entered ("DailyHours"). These are further broken down into weeks, where a production report can be generated. I'm wanting to use a hidden field to display their wrap time in DailyHours ("tenth_out") from the previous day, if they worked. Using this I can then calculate if enough time was given between wrapping on the previous calendar day and starting again on the current day. Hours are entered as tenths of an hour and can (and do) go over hour 24, so 1am would be 25, etc. if they worked that late.

Unfortunately, it's not possible to guarantee the users are entering data in sequential order, meaning they could go back and adjust times later, or add days that were missed after the fact. I'm wondering what the best method is to calculate this, whether it be specifically referencing the employee ID number and work date - perhaps using SQL?

The important fields in this scenario would be:

1) Days::Date and Days::EmployeeIDs
2) DailyHours::Date, DailyHours::EmployeeID, DailyHours::Tenth_In and DailyHours::Tenth_Out

Thank you

[comment]

I am not sure why you have an EmployeeIDs field in the Days table. Or why you need the Days table at all.

I think you should have a calculation field in the DailyHours table that calculates the date of the previous calendar day. Then construct a self-join matching the calculation field to the Date field and EmployeeID to EmployeeID. Then you can subtract the 2 values.

Added:
The above assumes that the date to compare to is always the previous calendar day. If that's not true (for example, if a violation can occur when someone worked until very late on Monday and started again on Wednesday, or when someone logged twice on the same date) then you will need a slightly more elaborate method:

Calculate the start and end timestamps of each entry. Define the self-join relationship as:

DailyHours::EmployeeID = DailyHours 2::EmployeeID
AND
DailyHours::StartTS > DailyHours 2::EndTS

and sort the DailyHours 2 by EndTS, descending. Then subtract the 2 timestamps.

 

 

Edited May 6 by comment

[madman411]

2 hours ago, comment said:

I am not sure why you have an EmployeeIDs field in the Days table. Or why you need the Days table at all.

I think you should have a calculation field in the DailyHours table that calculates the date of the previous calendar day. Then construct a self-join matching the calculation field to the Date field and EmployeeID to EmployeeID. Then you can subtract the 2 values.

 

 

Days table is used to generate statistics about the work day - who worked most, how many crew worked, filming location for that day, if mileage was applicable, etc. EmployeeID field was used to generate a list of IDs for another feature I worked on.

Your suggestion did work though. I was overcomplicating the way I was trying to do it before. Thank you!