Re: SQL scrolling back to a record - REVISTED

Thanks. I am storing all these suggestions (and my experience) for V 2.0.....

Try as I might, I still couldn't get a consistent data set from the approaches you suggested. I understood most of what was going on in the statement, it is just that it really isn't designed to handle all the exceptions. If I could have gotten it to the point where I had a consistent IN/OUT pair that were correct I would have run with it.

I reverted back to my original code which simply structured the data by employee/date/time and then read through, record by record. If an OUT didn't follow an IN, I did some error checking and additional record reading to make sure I wasn't missing some of the weirder situations. It is slow and probably inefficient, but my comparisons to the actual punches and my manual math seem to indicate that it is accurate (so far). There is more to the code than just totaling (there is approval and posting to payroll) so I am happy with the results so far.

Thanks again for the help. I learned much, which is always a good thing...

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/19/2012 9:02 PM, Charles Wilt wrote:

The answer to most those question is....it depends :)

I'd say the punch in always starts a new record...as would an out
without an existing in with an empty out...

You might automatically fill the missing out with a time, or you might
leave it NULL and thus easy to find for whomever is doing payroll.

The key thing is that you always look at in/out pairs...why not store
them that way?

You've really only got 2 choices, the clock asks the 20 questions or
whomever doing payroll does. :)

Storing pairs makes it easier to see when the 20 questions need be asked.

Charles

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

I did take a look at your original link and at least for the pages that
were available, and I get what he is saying but the world doesn't
consist of perfectly paired punches. Thus, if you create a single
record that has both punch in an out buckets, how do you handle the
punch in at 8am and forgot to punch out at lunch but I came back in at
13:00 and want to Punch in. Does that start a whole new record or does
the IN punch go into the OUT bucket of the first record? If I Punch IN
at 08:00 and then accidentally punch IN instead of OUT at noon, do I
start a new record, issue an error, or what? Maybe I left to do an
errand at 11:00 and now I really AM punching in at noon. What then?

Yeah, all these things could be handled with a web application or a more
interactive app but do you really want the clock to be asking 20
questions when there is a line of people waiting to punch out because
someone forgot to punch in or out? Sometimes the technically desirable
approach comes up against the capriciousness of a human. However, it is
good food for thought and I'll take a redesign into consideration as I
go forward.

Going back to your suggestion. It works! on this limited data set.
I'll try it on the couple of hundred thousand punch records I have and
see what happens.....

Thanks for the pointers.

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/19/2012 2:42 PM, Charles Wilt wrote:

Pete,

Having IN/OUT on one record makes it real easy for the DB to tell when
one is missing :) ....

See if you can get ahold of the Joe Celko book I had a link
too...timecards are a tough problem. He discusses the issues in
detail.

But Try this:
with tbl as ( select row_number()
over(partition by emp order by emp, dte, tim )
as punchNbr
, a.*
from timecard A where dte between '2012-05-17' and '2012-05-19'
)
, ins as (select * from tbl where punch='IN')
, outs as (select * from tbl where punch='OUT')
select ins.emp, ins.dte, ins.tim, outs.dte, outs.tim
from ins
left outer join outs
on (ins.emp = outs.emp
and ins.punchNbr + 1 = outs.PunchNbr)

Charles

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

I could change everything. I just don't want to :-) . I own the
solution from the timeclock (programmed by me) through the Java servlet
(also written by me). So, yeah, I could re-architect the whole tamale
but there is an awful lot of code there....

I am not sure what the IN/OUT on the same record does for me. Here is a
familiar scenario:

Policeman clocks IN at 20:00 on 7/10/2012 and then Clocks out at 01:00
on the 11th. He takes a call at 03:00 goes to the scene, does his work
and gets to the office and clocks out at 06:00. He never clocked in
because we went on duty without going to the station first. So he has a
punch IN (at 20:00) and a punch OUT at 01:00 and another punch OUT at
06:00. This is perfectly permissible. But it gets worse if he
accidentally punched IN at 06:00 am rather than OUT because he will
punch IN again at 20:00 to start his shift. Now I have IN/OUT/IN/IN.

I have pretty limited tools on the timeclock itself. The even driven
approach (tell me who what and when ) is the easiest to implement given
the programming tools I have. I could try something more sophisticated
on the clock but it will take some effort.

I was hoping to solve this on IBM i side.

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/19/2012 2:20 PM, Charles Wilt wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.