need help with timeclock app + cron

[newbiehacker]

I am using php timeclock and need a way to notify the hr dept if an employee does not clock out at night. My problem is that i cant figure out a way to write the mysql code for the cron job. the database is set up like this

fullname|inout|timestamp|notes|

I need the sql to find the employees that are still in the "out" status at a certain time. I am a newbie and would be thankful for any help.
Thanks

[fenway]

I don't understand... you're storing two records, one for in and one for out?

[newbiehacker]

yes, the inout field either holds "in" or "out" and I need to run the cron at around 1:00 in the morning to check which employees if any did not clock out for that day. the timestamp field hold a unix time stamp which is converted in the php timeclock app.

[newbiehacker]

here is a sample of the table. the ip adress does not matter. I use the employees extension for fullname
+----------+-------+------------+-------+---------------+
| fullname | inout | timestamp  | notes | ipaddress    |
+----------+-------+------------+-------+---------------+
| 200      | out  | 1152219879 | NULL  |              |
| 200      | in    | 1152219858 | NULL  |              |
| 200      | out  | 1152219659 | NULL  |              |
| 200      | in    | 1152219636 | NULL  |              |
| 200      | out  | 1152219405 | NULL  |              |
| 200      | in    | 1152219385 | NULL  |              |
| 200      | out  | 1152219118 | NULL  |              |
| 200      | in    | 1152219028 | NULL  |              |

[fenway]

You should be able to do a self-join to find "in" records without matching "out" records on any given day, assuming you don't cross midnight.

[newbiehacker]

Thank you for the suggestion, but I am quite confused on this self join. Ive looked at many sites and cant find a good example of this can you lend any more input on the situation.

[fenway]

Basically, you simply LEFT JOIN the table to itself, with the on condition being a shared fullname on the same day, and then look for non-matching "out" rows for every "in" row.