Re: SQL scrolling back to a record - REVISTED

[Pete Helgren <pete@xxxxxxxxxx>]

I ended up with this as an executable query :

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

The cast to an actual timestamp was problematic because of the format of 
the data.  But, the results were a little wonky....

  EMP   INTIME             OUTTIME
3,333   -                  10:15:00 05/17/12
1,111   22:20:00 05/17/12  10:20:00 05/17/12
1,111   09:05:00 05/16/12  04:07:00 05/18/12
1,111   -                  22:20:00 05/18/12
2,222   09:10:00 05/17/12  06:20:00 05/19/12
2,222   22:20:00 05/18/12  20:20:00 05/19/12

I am going to pursue the solution Charles gave me but I am still hanging 
on to this approach because it seems to some merit as well.  Just have 
to sort out the particulars.

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

On 7/19/2012 2:22 PM, Dan Kimmel wrote:
> 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.