Re: SQL scrolling back to a record - REVISTED

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.