-
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
-
-
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
-
How about adding simple WHERE condition?
... WHERE timeOut IS NOT NULL;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
That shows when clocked out, but not for someone who is still clocked in.
-
... WHERE timeOut IS NULL;
Jeff Hunter
-
in the case when
timeout is null (both max(timeIn) and max(timeout)=null
timeout is not null (just like STECAL said)
-
or, maybe you're looking for something like:
Code:
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
Jeff Hunter
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|