Okay, as I recall, you said that sometimes people would check in (or check out??) more than once. So your goal is to match up the first time a person checks in each day with the last time they checkout, right? Any other DTR records you will just ignore, yes?
So it's pretty simple:
(1) Use the simple query
SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR
(2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.
(3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.
(4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:
EMP_NO, CHECKIN_DTR, CHECKOUT_DTR
(5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).
Notice that if the EMP_NO changes, that is *automatically* a change of SHIFT.
So let's make up a sample:
Isn't it *OBVIOUS* when looking at those date/times that the following is true?
110011 Dec 3, 2011, 8:35 AM
110011 Dec 3, 2011, 9:05 AM
110011 Dec 3, 2011, 5:20 PM
110011 Dec 4, 2011, 9:20 PM
110011 Dec 4, 2011, 9:50 PM
110011 Dec 5, 2011, 3:50 AM
110011 Dec 5, 2011, 4:05 AM
220022 Dec 3, 2011, 8:40 AM
So by making one run through the "raw" DTR data, you should be able to create a table with BEGINSHIFT and ENDSHIFT and then you can do *ALL* your computations (e.g, total time worked, etc.) from that new table.
EMP_NO BEGINSHIFT ENDSHIFT
110011 Dec 3, 2011 8:35 AM Dec 3, 2011 5:20 PM
110011 Dec 4, 2011 9:20 PM Dec 5, 2011 4:05 AM
220022 Dec 3, 2011 8:40 AM ... etc. ...