Re: SQL scrolling back to a record - REVISTED

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.