Thanks Charles,

I'll have to walk through this with my live data adjusting for my column names. In fact, the schema is very similar with the punch being part of the record and the IN or OUT being recorded by the device.

Things get interesting when the punch records aren't done correctly such as:

(1111,'IN' , '9:05:00','2012-05-16'),
,(1111,'IN','11:50:00','2012-05-16'), <- Punched in again instead of out
,(2222,'IN' , '9:10:00','2012-05-17')
,(3333,'IN' , '9:11:00','2012-05-17')
,(1111,'OUT' , '9:11:00','2012-05-17') <- Was this supposed to be an IN?
,(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' , '9:07:00','2012-05-18')
,(3333,'IN' , '9:11:00','2012-05-18')
,(3333,'OUT','10:14:00','2012-05-18')
,(1111,'OUT','22:20:00','2012-05-18')

The challenge is that the pairings aren't always correct and discerning when the paring crossed a date border and WAS correct is devilishly difficult. But, I'll give your code a try when I get back to my office (in transit at the moment) and experiment a bit.

Thanks

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


On 5/18/2012 12:23 PM, Charles Wilt wrote:
Something to consider...
CREATE TABLE TIMECARD(
EMP INT NOT NULL WITH DEFAULT
, PUNCH CHAR (3) NOT NULL WITH DEFAULT
,TIM TIME NOT NULL WITH DEFAULT
, DTE DATE NOT NULL WITH DEFAULT)

(discarding identity field as it has no bearing :) )

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' , '9:07:00','2012-05-18')
,(3333,'IN' , '9:11:00','2012-05-18')
,(3333,'OUT','10:14:00','2012-05-18')
,(1111,'OUT','22:20:00','2012-05-18')


Now...
with tbl as (
select
(row_number()
over(partition by emp order by emp, dte, tim ) + 1) / 2
as setNbr
, a.*
from timecard A
)
, 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.setNbr = outs.setNbr)


If you're only pulling one employee at a time, add a WHERE emp = XXXX
in the first CTE that reads the timecard table.

Lastly, probably better to use the ins.dte as the for your report
filter...you should always have a IN punch, whereas you might not yet
have an OUT.

HTH,
Charles


On Fri, May 18, 2012 at 1:08 PM, Charles Wilt<charles.wilt@xxxxxxxxx> wrote:
Pete,

The problem is that you're thinking in records..instead of sets :)

Clock in / Clock out in not an uncommon issue..is suggest googling for
existing solutions...

Note you're doing this the hard way, having IN/OUT as separate
rows...any chance you can change the table structure?
http://books.google.com/books?id=90c41yKz3IUC&pg=PA194&lpg=PA194&dq=SQL+timecard+Celko&source=bl&ots=FdbiQHEJRa&sig=eWDad8LlG4pb9CFY_iS38NzM5ek&hl=en&sa=X&ei=Q3-2T43TMorS2QWQ2_2_CQ&sqi=2&ved=0CEwQ6AEwAQ#v=onepage&q&f=false

let me know if the link doesn't work and I'll pm you a screen shot...

Charles




On Fri, May 18, 2012 at 12:30 PM, Pete Helgren<pete@xxxxxxxxxx> wrote:
I hear you SETGT with a READEP would be the way to go...but this is a
Java program using SQL so things get a bit more interesting:

For the others who have weighed in. The reason for the logic is that I
have a program the calculates the number of minutes a person has worked
based on a time clock punch. A good example of a condition I am try to
capture is a worker who clocks in at 22:00 on 05-16-2012 and then clocks
out 0600 on 05-17-2012. When a report is run for 05-17-2012 this
employee would have a punch OUT record with no prior punch IN record
which could be an error or could be a valid punch. The only way I can
tell is to check to see what the immediately prior punch for that
employee would be. There could have been hundreds of other punch
records for other employees between the last punch and the prior punch.

So I thought a scrollable cursor would be the way to go. Grab all the
records for that employee (could be thousands), locate the specific
punch I have a question about (the one at 0600 on 05-17-2012), then walk
back one record and see what the punch is (should be an IN). The
problem is (as I discovered last night) that you can't position a
scrollable cursor by a key value. You can position by a fixed record
number or a relative number of records but you can't locate a record in
the record set by a column value (as far as I can tell).

One way to reduce the number of records would be to just bracket the
date selection to only include one day prior (otherwise that would be a
LOOONG shift) so that could reduce the records on a specific employee so
that walking through the record set to find the record I was interested
in and then walking back one.

I *think* I can just select the last record for the prior day and see if
that is the "IN" punch I am looking for but I need to walk through some
other transactions to see if this will produce a "false positive"

Still sorting through the options but appreciate the feedback so far

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


On 5/18/2012 7:14 AM, sjl wrote:
Sigh...
RLA makes this so much easier!;-)
- sjl
--
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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].