CODEWITHSUNDEEP
mysqlsql
Jaquarh
Jaquarh

Reputation: 6693

Checking if a user is clocked in or out

I have a table of employees which holds basic information like forename, lastname etc.. Each employee is given a unique ID.

I then have two tables, rwh_clock_in and rwh_clock_out which both look like this:

id (int) 255 PRIMARY A_I
atTime (timestamp) DEFAULT_TIMESTAMP
userid (int) 255

I am trying to select the users information from the database and then also check if the user is clocked in or clocked out.

SELECT employee.forename, employee.surname, employee.contactNumber, employee.contactEmail
(CASE WHEN clockin.atTime > clockout.atTime THEN 0 ELSE 1 END) AS 'clockedIn'
FROM rwh_employers employee
LEFT JOIN rwh_clock_in clockin
ON employee.id = employee.id AND employee.id = clockin.userid
LEFT JOIN rwh_clock_out clockout
ON employee.id = employee.id AND employee.id = clockout.userid AND clockin.userid = clockout.userid
WHERE employee.id = 1

However, I receive an SQL error that reads:

1305 - FUNCTION employee.contactEmail does not exist

My rwh_employers table does in fact have a contactEmail column that is VARCHAR (120).

I thought about it logically, I won't know if the user has clocked out or not if I do not check if there is any rows that is after the atTime from the rwh_clock_in in the rwh_clock_out. So, I need to firstly check for any clock outs from that userid after the clock in times. If it exists then the user is not clocked in, if it doesn't then the user is clocked in.

Any help would be appreciated.

UPDATE:

SELECT employee.forename, employee.surname, employee.contactNumber, employee.contactEmail, clockin.atTime,
(CASE WHEN clockin.atTime < clockout.atTime THEN clockout.atTime ELSE NULL END) AS 'clockOut'
FROM rwh_employers employee
LEFT JOIN rwh_clock_in clockin
ON employee.id = employee.id AND employee.id = clockin.userid
LEFT JOIN rwh_clock_out clockout
ON employee.id = employee.id AND employee.id = clockout.userid AND clockin.userid = clockout.userid
WHERE employee.id = 1
ORDER BY clockout.atTime ASC LIMIT 1

Now, the clockout is always NULL even when the user is clocked out

Upvotes: 0

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You seem to want the latest time for a given user from each table. I would suggest something more along these lines:

select cio.user_id,
       (case when last_ci < last_co then 'checked_out'
             else 'checked_in'
        end) as which
from (select 1 as user_id,
             (select max(atTime) from rwh_clock_in where user_id = 1) as last_ci,
             (select max(atTime) from rwh_clock_ou where user_id = 1) as last_co
     ) cio;

Upvotes: 1

Related Questions