How to get last timeIN/Out ?

[ocpdude]

if I have this
empid--timeIn----------------------timeOut
1-------18-JUL-02 08:55:48---18-JUL-02 08:56:33
1-------18-JUL-02 10:49:00---18-JUL-02 12:49:00
1-------18-JUL-02 01:49:00------null

how can I get the last timeIn and the last timOut ?

Thanks

[stecal]

select max of each.

[ocpdude]

Hi,

select max doesn't solve the problem.. let me try to explain
it further.

the idea is the employee will be clocking In and out during the
day and I am trying to find when he last clocked In and if he clocked out.
In my previous example.. the emp is still clocked so if I use max I'll get 18-JUL-02 01:49:00 for timeIn and
18-JUL-02 12:49:00 for timeOut wich is not true. because he
is not clocked out yet.

I hope this helps

[jmodic]

How about adding simple WHERE condition?

... WHERE timeOut IS NOT NULL;

[stecal]

That shows when clocked out, but not for someone who is still clocked in.

[marist89]

... WHERE timeOut IS NULL;

[ocpdude]

in the case when
timeout is null (both max(timeIn) and max(timeout)=null
timeout is not null (just like STECAL said)

[marist89]

or, maybe you're looking for something like:

SQL> select * from xyz;
     EMPNO CLOCK_IN            CLOCK_OUT
---------- ------------------- -------------------
         1 07/17/2002 15:09:17 07/17/2002 23:09:17
         1 07/17/2002 15:09:19 07/17/2002 23:09:19
         1 07/17/2002 15:09:26 07/17/2002 23:09:26
         1 07/17/2002 15:09:39
         2 07/17/2002 15:09:52 07/17/2002 23:09:52
         2 07/17/2002 15:10:53
         3 07/17/2002 15:20:59 07/17/2002 23:20:59

7 rows selected.

SQL> select e.empno, e.clock_in, e.clock_out
  2  from xyz e, (select empno, max(clock_in) clock_in from xyz  group by empno) a
  3  where a.empno = e.empno
  4  and a.clock_in = e.clock_in
  5  /

     EMPNO CLOCK_IN            CLOCK_OUT
---------- ------------------- -------------------
         1 07/17/2002 15:09:39
         2 07/17/2002 15:10:53
         3 07/17/2002 15:20:59 07/17/2002 23:20:59

[ocpdude]

Thank you