I am looking to establish an easy approach to query two tables: one with staff Clock in + out times (a log of all users) and the other with a weekly rota of shifts. I would like to check that a user checked into work on their shift within 30 mins of their shift start time, and shift end time. Ther rota table has Shift start and end time field with date and time - and the clock in and out table has the same.
What is the best approach here please? Is it best to have the data in two tables or in one perhaps?
It should be quite doable, but you need to define your exact requirement a bit more.
For example - is this just for a given date? For today? For a range of dates? How would you present the result on the user interface? What input will you be taking from the App user?
It is simply for internal reporting really. The used case is for accounts teams to cross reference that a user’s shift in the rota table was 10am on 3rd Feb for example, and when they cross reference the Clock in and out table for staff, the check in time is within 30 mins of the rota shift time start? Does this make sense please?
It makes sense, but I’m still not clear how your accounts team would interact with it.
For example, would you want to present a whole range of dates with clock in/out times on a single screen, or would they just be viewing data for a single date?
This is important, because if it’s just a single date then it should be easy. But if it’s a range of dates then you may need to introduce a helper table. But either way, the approach would be slightly different.
It would help if you can show how the two tables are structured, and what your accounts people would be seeing.
Thanks, I would imagine a whole range of dates as then you could cross check the times )or flag via IF function) if they were inside or outside the 30 mins please.