CODEWITHSUNDEEP
mysqlsql
user674311
user674311

Reputation:

Find missing MySQL Data

So, we have a table, called timePunches:

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

This table is used for tracking punch times from our employees. Meaning, we track when they punch in and punch out. This table is an audit compliance table, meaning that we do not SELECT/UPDATE records, instead, we SELECT/INSERT them only.

So, this is what happens when a user punches the clock:

As we can see from the above, when the user punches IN, the query looks kind of like this:

INSERT INTO timePunches (punchID, employeeID, punchDATETIME, punchDTC_LINK, punchDATECRC, punchDIRECTION, punchTOTAL) VALUES (15797, 'prumple', '2012-01-11 17:35:10', NULL, -2011509138, 'IN', NULL);

And, when the user punches out, it sends something like this:

INSERT INTO timePunches (punchID, employeeID, punchDATETIME, punchDTC_LINK, punchDATECRC, punchDIRECTION, punchTOTAL) VALUES (15797, 'prumple', '2012-01-11 19:39:52', '2012-01-11 17:35:10', -2011509138, 'OUT', NULL);

So, as we can see, there are 2 elements in the DataTable for this user. One IN and one OUT.

What I need to do, is find out if the user has forgotten to punch OUT. So, let's say that a user punches IN, works all the day long, closes their punch clock w/o clocking out, and then the next day punches IN again. HE then works his full shift, and remembers to punch out this time.

Now, for that situation, we have 2 IN punches, and only 1 OUT punch. I need a way to detect this.

Upvotes: 1

Views: 387

Answers (4)

em3ricasforsale
em3ricasforsale

Reputation: 352

Since you match it up with the last IN, search for the last in and append the OUT to it. But what I honestly don't understand, is why you have to match the IN and out on a single record.

I have had to develop a timekeeping system each day to be paid has to have an IN and an OUT. Our time keeping system has individual recored for each punch IN and OUT. You could search for if current day has two INs in a row OR two OUTs in a row ,then there is a mispunch or if previous day has two INs in a row OR two OUTs in a row OR doesn't start with and IN OR doesn't end without and OUT then there is a mispunch

Update:

I understand that, but why does it have to store the IN punch information in the out punch line? The time for a punch is always always in sequencial order. So if the time has them stored inorder of IN IN or IN OUT that is the easiest way to tell.

Example of what I was suggesting storing it as:

IN 2012-01-08 08.00 AM

IN 2012-01-08 11.00 AM

OUT 2012-01-08 15.00 PM

Your check would be to see if the day has two INs in a row that are in time order as well. If you want to do it your way, then match all of the single INs up with a paired INs and get the not not matched up so basically you would do a Left join of your Only in punches to the ones on the right being INs with OUTs then your list on the left that have null values in the match up would show you your mispunches.

Upvotes: 0

pilcrow
pilcrow

Reputation: 58534

If I understand correctly, an 'IN' record's corresponding 'OUT' record will match on employeeID (the punch is for the same employee) and the 'IN' DATETIME will match the 'OUT' DTC_LINK.

-- Fetch all 'IN' records without a corresponding 'OUT'
    SELECT tp_in.*
     FROM timePunches tp_in
LEFT JOIN timePunches tp_out
          ON tp_in.employeeID = tp_out.employeeID
             AND
             tp_in.punchDATETIME = tp_out.punchDTC_LINK
    WHERE tp_in.punchDIRECTION = 'IN'
          AND
          tp_out.punchDTC_LINK IS NULL;

Upvotes: 1

Horus
Horus

Reputation: 1175

You would left outer join the table to itself:

select
   a.punchId
from
   timePunches a
   left outer join timePunches b on
       a.punchId = b.punchDTC_LINK
where
   b.punchDTC_LINK is null
   and a.punchDirection = 'IN';

This will return the stuff you are looking for, I think.

I edited to add the check for punchDirection of in.

You can also change the select piece to select out a.*, which would get you everything from the record.

Additional Edit:

The code above appears to have solved the original problem but what the real problem is when someone checks in twice in a row, then checks out. In MYSQL, this is an incredibly difficult problem, but in Postgres & Oracle, it is relatively simple, and can be solved with windowing functions. I know that this does not solve your problem, but you can probably work out the problem with a script after the fact. In postgres/oracle, the following would find the records that you are looking for:

select * from
(select
   a.punchId,
   lag(b.punchDTC_LINK,1) over (
      partition by a.employeeId
      order by a.punchDATETIME
   ) prev_link,
   a.punchDTC_LINK cur_link
from
   timePunches a
   left outer join timePunches b on
      a.punchId = b.punchDTC_LINK)
where prev_link is null and cur_link is not null;

Or something similar. I haven't tested it.

As for a script, you could use the following query:

select
   a.punchId,
   a.employeeID,
   a.punchDATETIME,
   a.punchDIRECTION,
   b.punchDATETIME,
   b.punchDIRECTION
from
   timePunches a
   left outer join timePunches b on
       a.punchId = b.punchDTC_LINK
order by a.employeeID, a.punchDATETIME desc;

and by looping over the data, and/or writing a script to loop over the data and find places where the b.punchDIRECTION is null and it is not the first record for a given employee, you will easily be able to find the bad records.

Upvotes: 0

Thom Wiggers
Thom Wiggers

Reputation: 7034

You said "You see, when a user punches in, it populates all of the necessary data, except for the punchDTC_LINK field; this is left as 'null', because that field is used for previous start time. "

That should mean you can just do a WHERE punchDIRECTION = "IN" AND punchDTC_LINK = NULL

Upvotes: 0

Related Questions