StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFind missing MySQL Data
text
Body
copied!<p>So, we have a table, called <code>timePunches</code>:</p> <pre><code>CREATE TABLE `timePunches` ( `punchID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the punch', `employeeID` VARCHAR(50) NOT NULL COMMENT 'Who did the punch', `punchDATETIME` DATETIME NOT NULL COMMENT 'The time of the punch', `punchDTC_LINK` DATETIME NULL DEFAULT NULL COMMENT 'The previous start time for the OUT punch', `punchDATECRC` INT(100) NOT NULL COMMENT 'The punch CRC, to prevent hacking', `punchDIRECTION` TINYTEXT NOT NULL COMMENT 'What the punch did', `punchTOTAL` INT(11) NULL DEFAULT NULL, `fullName` TEXT NULL, PRIMARY KEY (`punchID`) ) COLLATE='latin1_swedish_ci' ENGINE=MyISAM ROW_FORMAT=DYNAMIC AUTO_INCREMENT=15825 </code></pre> <p>This table is used for tracking punch times from our employees. Meaning, we track when they punch <strong>in</strong> and punch <strong>out</strong>. This table is an audit compliance table, meaning that we do not <strong>SELECT/UPDATE</strong> records, instead, we <strong>SELECT/INSERT</strong> them only. </p> <p>So, this is what happens when a user punches the clock:</p> <ul> <li><p><strong>PUNCH IN:</strong> Application sends <code>employeeID</code> (string), <code>punchDATETIME</code> (DateTime), <code>punchDATECRC</code> (int) and <code>punchDTC_LINK</code> (DateTime [null]) to the database via a stored procedure. This stored procedure adds the necessary information to the database, to include triggering an internal function to pull the full name of the user into the same table.</p></li> <li><p><strong>PUNCH OUT</strong>: Application sends <code>employeeID</code> (string), <code>punchDATETIME</code> (DateTime), <code>punchDATECRC</code> (int) and <code>punchDTC_LINK</code> (DateTime) to the database. The stored procedure adds the necessary information, including the trigger to do the math between both <code>DateTime</code> elements, and fill out the legal name. </p></li> </ul> <p>As we can see from the above, when the user punches <strong>IN</strong>, the query looks kind of like this:</p> <blockquote> <p>INSERT INTO <code>timePunches</code> (<code>punchID</code>, <code>employeeID</code>, <code>punchDATETIME</code>, <code>punchDTC_LINK</code>, <code>punchDATECRC</code>, <code>punchDIRECTION</code>, <code>punchTOTAL</code>) VALUES (15797, 'prumple', '2012-01-11 17:35:10', NULL, -2011509138, 'IN', NULL);</p> </blockquote> <p>And, when the user punches <strong>out</strong>, it sends something like this:</p> <blockquote> <p>INSERT INTO <code>timePunches</code> (<code>punchID</code>, <code>employeeID</code>, <code>punchDATETIME</code>, <code>punchDTC_LINK</code>, <code>punchDATECRC</code>, <code>punchDIRECTION</code>, <code>punchTOTAL</code>) VALUES (15797, 'prumple', '2012-01-11 19:39:52', '2012-01-11 17:35:10', -2011509138, 'OUT', NULL);</p> </blockquote> <p>So, as we can see, there are 2 elements in the DataTable for this user. One <code>IN</code> and one <code>OUT</code>. </p> <p>What I need to do, is find out if the user has forgotten to punch <code>OUT</code>. So, let's say that a user punches <code>IN</code>, works all the day long, closes their punch clock w/o clocking out, and then the next day punches <code>IN</code> again. HE then works his full shift, and remembers to punch <code>out</code> this time.</p> <p>Now, for that situation, we have 2 <code>IN</code> punches, and only 1 <code>OUT</code> punch. I need a way to detect this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload