I have a table of time punches. They need to be matched up, “in” punch with “out” punch in order to locate any missing punches. I have a way to do that, but it’s slow, 45ish seconds for 700ish records, aka way too slow.
This is simple version of the punch table.
create table tbl_punches (
LocationID int NOT NULL,
UserID int NOT NULL,
PunchType varchar(3) NOT NULL, -- "IN" or "OUT"
PunchDT datetime NOT NULL
To make the missing punch report, I have a stored proc with a cursor call that looks kind of like this (I’m simplifying it for here):
FOR select LocationID,
PunchDT as InPunchDT,
NULL as OutPunchDT
where PunchType = 'IN'
NULL as InPunchDT,
PunchDT as OutPunchDT
where PunchType = 'OUT'
order by LocationID, UserID, InPunchDT,
Then I step through the cursor and put the report together by matching the “In” and “Out” records to build a single record that is inserted in a temp table. In the end, pull the contents of that temp table and that’s the missing punch report.
Is there a faster way to do this kind of thing, maybe without a cursor?