RE: SQL scrolling back to a record - REVISTED

Why not use timestamp arithmetic and an embedded query?

with tbl as (select emp, timestamp(tim || ' ' || dte) as clocktime
from timecard A) tbl
, ins as (select * from tbl where punch='IN')
, outs as (select * from tbl where punch='OUT')
select outs.emp, outs.clocktime as outtime,
(select min(ins.clocktime) from ins where ins.emp = out.emp and ins.clocktime > outs.clocktime) as intime
from outs

I'm not sure about the arguments in that timestamp function, but the rest should work.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Thursday, July 19, 2012 2:57 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL scrolling back to a record - REVISTED

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.

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

Although I might not be able to solve the "scroll back" issue with additional modifications to the SQL statement, I would at least like to have the "orphaned" OUT not matched up with the wrong IN which is what is happening now.

Not sure I can solve this with SQL but I sure am close.....

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=S
QL+timecard+Celko&source=bl&ots=FdbiQHEJRa&sig=eWDad8LlG4pb9CFY_iS38N
zM5ek&hl=en&sa=X&ei=Q3-2T43TMorS2QWQ2_2_CQ&sqi=2&ved=0CEwQ6AEwAQ#v=on
epage&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 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.