I have a problem at hand
I need to calculate the total time per day, per month for each employee based on their attendance(time in and time out).
I am using a table with 4 columns
id(pk),rfid_no, att_date(timestamp),status(0 for in and 1 for out)
each time the rfid_no is entered the timestamp is registered and the status has to show in and out
I am still working on the PHP code to automate the status.
If you have any input on that please help as well.
The RFID_No is like the emp_id
below is my query for finding the total working hours.
I also need the total working days.
SELECT c.firstname,a.rfid_no, FLOOR(sum(unix_timestamp(b.att_date)- unix_timestamp(a.att_date))/3600) as working_hours FROM timesheet a
join users c on c.rfid_no = a.rfid_no
join timesheet b on (a.rfid_no = b.rfid_no && a.status = 0 && b.status = 1 )where (a.id+1) = b.id
It returns only one rfid_no and the total time is completely wrong.
Please help!!!! Thank you in advance.
if you need more input please ask.