madman411 Posted May 6 Share Posted May 6 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 Link to comment Share on other sites More sharing options...
comment Posted May 6 Share Posted May 6 (edited) 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 1 Link to comment Share on other sites More sharing options...
madman411 Posted May 6 Author Share Posted May 6 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now