Re: SQL scrolling back to a record - REVISTED

Pete,

Trying to work around bad data is always hard...

Much better to prevent the bad data from making it to the DB in the
first place :)

But with the structure you've got, that's hard.

Did you reply originally that you couldn't change the table to have in
and out on a single row?

Charles

On Thu, Jul 19, 2012 at 3:56 PM, Pete Helgren <pete@xxxxxxxxxx> wrote:

After looking at a few other approaches, I revisited the approach that
Charles put together. I originally thought this was going to work OK
but there seems to be a wrinkle when the punch IN/OUT pattern is more
variable. Making a slight modification to the data set (based on real
punches I see in the customer database):

insert into timecard
values
(1111,'IN' , '9:05:00','2012-05-16')
,(2222,'IN' , '9:10:00','2012-05-17')
,(3333,'IN' , '9:11:00','2012-05-17')
,(3333,'OUT','10:15:00','2012-05-17')
,(1111,'OUT','10:20:00','2012-05-17')
,(1111,'IN','22:20:00','2012-05-17')
,(1111,'OUT', '4:07:00','2012-05-18')
,(1111,'IN' , '9:07:00','2012-05-18')
,(3333,'IN' , '9:11:00','2012-05-18')
,(3333,'IN','10:14:00','2012-05-18')
,(1111,'OUT','22:20:00','2012-05-18')
,(2222,'IN','22:20:00','2012-05-18')
,(2222,'OUT','6:20:00','2012-05-19')
,(2222,'IN','12:20:00','2012-05-19')
,(2222,'OUT','20:20:00','2012-05-19')


In this data set, you will see that EMP 2222 will have a punch IN time that occurs after the punch OUT in the set.

The punch pattern that seems to be causing a problem is when a punch wraps over a day AND the reporting date range omits the prior punch IN (or the employee punches wrong) so all we have is an OUT and then an IN and OUT on the same date.

This brings me back to the need to "scroll" back when this error occurs. If my first punch on a day is an OUT, I want to look back to the very last punch on the prior day to see if it was an IN. If so, I can include that IN punch in my calculations. But I ONLY want to do that when the first punch of the day is an OUT. That was my original issue. Run this SQL statement against the data set above and you can see what I mean in the very first record displayed.