I am developing a web system where I must register the attendance but I must validate the employee's registration time and this must determine if it is in the range of the registration time.
Suppose that in my database I have the time table in which its attributes are the following along with its records, in this case it can vary since some workers have more hours of entry than others.
HoursEntry = 08:30:00,12:00:00,15:00:00
DepartureHours = 11:00:00,14:00:00,16:00:00
And another table called attendance where I record the arrival and departure times of the employee. Its attributes are:
- employeeid : stores the employeeid
- dateRegistration : the time you registered your attendance
- status: registered if the assistance was delayed, not registered or punctual
Based on these data, when the employee arrives at the establishment, he must register his attendance, for that I obtain the data from the table where the entry and exit times are. Example if the employee arrives and checks in at 08:00:00 then I must go through my array of times of entries, and see what time is closest.
My example array would be this: 08:30:00,12:00:00,15:00:00 (it is worth mentioning that the hours are different per employee, that is, it will be dynamic)
So the problem arises when I want to know the registration time is close to the array and thus verify if it arrived on time or had a delay.
First I think you will need to modify your DB schema a bit.
In my opinion the data should have this structure:
To determine if a date is in the correct range (Pseudocode):
The code could be something like this:
If you need to compare multiple ranges you can simply loop the logic
for
I hope this works as a basis for the problem you need to solve
Try this: