2

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:

enter image description here

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:

People

enter image description here

Shifts

enter image description here

shift_breaks

enter image description here

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.

1
  • When I worked retail, the number of breaks were associated to the expected work day. Something like every N hours, you'd get a 15 minute break. That said, providing table structure and sample values will get your question into an actionable state. You've got expected output, a source query and have identified your database so you're well on your way.
    – billinkc
    Mar 29 '17 at 18:44
2

I think I understand what you are getting at. Could you try the following query:

SELECT startshift, endshift, starttime, endtime, `NAME`
from shifts
JOIN people on shifts.PPLID=people.id
INNER JOIN shift_breaks on shifts.PPLID=BREAKID 
    and (date(startshift) = date(starttime))
    and (date(startshift) = date(endtime))
where PPLID="0"
ORDER BY STARTSHIFT

This ensures that your join between shifts and shift_breaks are on Person ID AND the corresponding date.

I tested this in a database locally and it seemed to give the desired result.

This approach would have an issue if the start of the shift and end of the shift were on DIFFERENT dates (i.e.: overnight swing shift.)

Overnight shift wont work because we are comparing the DATE of the break begin/end to the DATE of the shift start. To handle spanning dates, you may need a SHIFT table with SHIFT ID, SHIFTSTART (datetime), SHIFTEND (datetime)... then Breaks would have a FK relationship to a SHIFT and EMPLOYEE. Shifts would have an FK relationship with EMPLOYEE. Would then join on IDs.

There are a few ways to restrict the records to a date range within the where clause. You can add where PPLID="0" and date(startdate) = '2017-03-30' for a single day or BETWEEN for a range (between '2012-03-11 00:00:00' and '2012-05-11 23:59:00').

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.