question

hollowspy.drl avatar image
hollowspy.drl asked

How to: sum timestamp from same column using another column as a reference

Hi Everyone,

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.

sqlstatisticssuminner jointimestamp
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Don't know if it's it because it's late on Friday and my brain is frazzled but I'm not sure that this is the best design for the following reasons:

  1. What if some leaves and doesn't sign out or vice versa, won't this mess up your joins because there will be no logged in / logged out status. We've all worked in buildings where people tail gate through doors with card scanners.
  2. I can't even think of at this moment how to get the correct matching record for someone. I don't a.id + 1 = b.id cuts it. Surely if anyone logs in / out between the event of person 1 then it wont work. (like i say my brain is frazzled so maybe it does work.

I think having, id (pk), rfid, LoggedInTime, LoggedOutTime is a much better design because you can simply find the last record where they havent logged out and set it. Getting the amount of time is then just a DateDiff() - also you have the flexibility to correct the logged in / logged out if that person is tail gating or whatever!

That's just my two cents. Maybe someone else has a better solution!

0 Likes 0 ·

Forgot to say - attaching some sample data that you already might help someone come up with a solution.

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.