June 29, 2009 at 1:39 pm
I have two tables that get hours entered into them. One has records coming from our system based on login and out times, these are type 'S'.
The other table has records which have been manually entered by someone. The records from both these tables get combined into one timesheet table.
The issue I have is sometimes the manual entries type 'M' overlap or fall entirely in entries from the system. I need a way to adjust the manual entries so they do not overlap system entries by adjusting their time_out to be the same as an overlapping time_in from the system. Also if the manual entry falls completely within system time, however the time_out on the manual entry is beyond the time_out on the system entry a record should be inserted containing any remaining manual time. If a manual entry is covered completely by system time then the manual entry should be removed (or start and end time set to be the same time so the difference is zero).
Example of what would be in the combined table.
rec_id empid source time_in time_out other_data
1 1 S 9:00 11:00 asdf
2 1 M 11:15 12:00 fdsa
3 1 M 12:30 14:00 jklm
4 1 S 13:45 14:00 psql
5 1 M 14:00 16:00 eski
6 1 S 15:00 15:45 jlem
Record 3 would get time_out adjusted to 13:45 since it overlaps record 4.
Record 5 would actually get broken up into two records. One going from it's time_in of 14:00 to the time_in of the overlapping system entry time_in of 15:00, and then a new record would be added with any information from the manual entry and a new time_in of 15:45 to time_out of 16:00.
The final table should look like my example below.
rec_id empid source time_in time_out
1 1 S 9:00 11:00
2 1 M 11:15 12:00
3 1 M 12:30 13:45
4 1 S 13:45 14:00
5 1 M 14:00 15:00
6 1 S 15:00 15:45
7 1 M 15:45 16:00
I'm honestly not even sure where to begin on this task. I can't imagine i'm the first to have such a requirement. Any help/suggestions would be greatly appreciated!
June 29, 2009 at 1:50 pm
This appears to be more of a data entry or application based resolution.
It seems that the easiest approach is to have the user entering dates manually be warned about the overlap before confirming the record entry.
If there is an overlap, just show the entering user which record(s) overlap and have the option to abort or adjust an existing record.
If that is not possible, you would have to know the rules about what to do in an inconsistency situation. Do you take the earlier start time, the later start time, the less hours, the more hours, combine time spans and the like. Once you know this, you should be able to run the data through a quick filter when merging.
June 29, 2009 at 1:53 pm
The entry coming from the system log in or out should always take priority, it is the manual entry which would be adjusted.
Sadly I do not have control over the application that allows the manual entry to be made as it is in a completely different system and database even, those records are just getting imported to my database each morning.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply