DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to get last timeIN/Out ?

  1. #1
    Join Date
    Nov 2000
    Posts
    198
    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    select max of each.

  3. #3
    Join Date
    Nov 2000
    Posts
    198
    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


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    That shows when clocked out, but not for someone who is still clocked in.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ... WHERE timeOut IS NULL;
    Jeff Hunter

  7. #7
    Join Date
    Nov 2000
    Posts
    198
    in the case when
    timeout is null (both max(timeIn) and max(timeout)=null
    timeout is not null (just like STECAL said)


  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  9. #9
    Join Date
    Nov 2000
    Posts
    198

    Talking

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width