I'm stumped lol which isn't that big of a surprise when it comes to some of the advanced sides of MySQL.
Our table consists of:
punchDATETIME and punchDTC_LINK, both are DATETIME columns and one punchDIRECTION column, which is a simple varchar(6) column with either IN or OUT in it., and finally a employeeID which is varchar(250) for usernames.
punchDATETIME is when the punch took place.
punchDTC_LINK is when the previous punch IN took place.
What i want to do is to be able to find missing punches. Meaning, if there is a IN punch for a user, but no OUT punch w/ the matching IN punchDATETIME, i want to return that.
I know that sounds confusing, so here's a bit of an example:
Would be one row. From that, you can see that there is no punchDTC_LINK on it at all, because that part is null.
INSERT INTO `timePunches` (`punchID`, `employeeID`, `punchDATETIME`, `punchDTC_LINK`, `punchDATECRC`, `punchDIRECTION`, `punchTOTAL`) VALUES (15797, 'prumple', '2012-01-11 17:35:10', NULL, -2011509138, 'IN', NULL);
Now, if i forget to clock out today, and i log into the system tomorow, there will be another punch with an IN on it, but no respective punch OUT. So, i need to find a way of finding the punches that are missed.
How can i do that?