Results 1 to 2 of 2

Thread: Time Keeping

  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: Time Keeping

    Hi there!

    How can I get the time in and time out of the employees without any time in and time out identifier on the database. Say i only have the time logs of the employees and their shift on different table.

    Code:
    Table A
    
    Employee_nr     Time Log          ShiftCode
    111                   8AM                 A
    111                   9AM                 A
    112                   8:15 AM            A
    113                   10AM                B 
    111                   5:45 PM            A
    112                   6PM                 A
    112                   6:30PM             A
    113                    6PM                B
    
    *TimeLog column is datetime
    
    Table B
    ShiftCode       StartTime        EndTime
    A                      8AM              5PM
    B                      10AM             6PM
    Thanks by the way

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Before you get to a solution, let's clean up what you have presented.

    You've given us some sample data, but the time fields appear to be indicating that you will be storing them as strings (given that, in some cases, you have a space between the time and the AM/PM, and in other cases you do not). So, the first thing you should do is to use one of SS date and/or time data types.

    Which brings-up the next question, why are you not recording the dates along with the time?

    Next, is this what is happening? . . .

    Employee 111 is scheduled for the 8AM-5PM shift.
    He checks in on-time for his shift.
    He checks in an hour late for his next sift (presumably for the next day?)--this is where a date would com in handy. We also presume that he forgot to check-out for his previous shift?
    Then, he checks-out for that second shift at 5:45?
    Employee 112 is scheduled for the 8AM-5PM shift.
    He checks-in and checks-out on his first shift.
    He fails to check-in on his second shift, but checks out at 6:30 on that shift.
    Employee 113 is scheduled for the 10AM-6PM shift.
    He checks-in and checks-out on-time. But are they the same shift? (another example where date would be indispensible).
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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