I am working on a project that needs a time clock solution. The point of sale software they use has a clock in/clock out function built in. But if an employee misses a punch or something needs to be modified the software does not have that built in. It also has a function of logging what type of break an employee takes.
My issue is this: The punch in and out data is in one table, the break punch in and out data is in another table.
Here is the data that I have in the database now. I am just learning this and playing with fake data:
The startshift is the punch in time, the end shift is the punch out time. Those are in 1 table. The start time and the endtime are a break. The name is the employee.
There should only be one break on one day but the line show up on every row pulled from the one table.
Here is my SQL. I apologize that this is probably a really newbie thing, but I am trying to help out a friend.
SELECT startshift, endshift, starttime, endtime, `NAME` from shifts JOIN people on shifts.PPLID=people.id JOIN shift_breaks on shifts.PPLID=BREAKID where PPLID="0" ORDER BY STARTSHIFT
Here are my table columns:
The query returned the results I have above. If you notice each line has a different startshift, and endshift, but the starttime and end time which are the actual break taken shows up in each row returned. It should actually show up only in one row as only one break was taken in those 3 days. When a person punches in the time gets logged into shifts as startshift, when they punch out it gets logged into shits as endshift. If they punch out for a break it gets logged into shift_breaks as starttime. when they punch back in from a break it gets logged into shift_breaks as endtime.