Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    get the datetime range of rows from one column

    Good day!

    I have table attendance
    fields are:

    EMP_NO
    DTR

    The DTR data has a data of In and Out of employee...

    Like for example: Schedule 05:35:00 - 13:35:00

    EMP_NO: 1001 DTR: 2011-10-24 05:35:10 //IN
    EMP_NO: 1001 DTR: 2011-10-24 05:35:15 // IN
    EMP_NO: 1001 DTR: 2011-10-24 13:35:00 // OUT
    EMP_NO: 1001 DTR: 2011-10-24 13:40:20 // OUT


    As you can see the employee twice IN and also he out twice. How can I get the first In and the last Out? And also how can I get the total hours of employee?

    Thank you so much...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    First IN and last OUT:

    Code:
    SELECT EMP_NO, MIN(DTR) AS FirstIn, MAX(DTR) AS LastOut
    FROM attendance
    GROUP BY EMP_NO
    ORDER BY EMP_NO
    What is your definition of "total hours" of an employee? Is it the time between FirstIn and LastOut? What is the point of the extra IN and OUT in there?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (11-21-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    I should note that if you have data for more than one day in the table, you will get the first in ON THE FIRST DAY and the last out ON THE LAST DAY.

    That's surely not what you want.

    If you do have data like that, say so.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Good day!

    I resolved it using this code:

    PHP Code:
    INSERT INTO dtr_total(EMP_NOTotalSELECT a.EMP_NOTIMEDIFF(max(b.ATTENDANCE), min(a.ATTENDANCE))
    FROM test_att a
    LEFT JOIN test_att b ON a
    .EMP_NO b.EMP_NO
    table:test_att
    columns: EMP_NO,DATE, ATTENDANCE

    table: dtr_total
    columns: EMP_NO, Total, Rendered

    Now I don't know how can I get the rendered, the rendered is timedifference between IN and OUT but only get the difference between their schedule

    like for example my shift is 05:35:00 - 13:35:00

    my IN = 05:35:00 and OUT = 14:35:00 Total will be = 9 hours because he out late. and Rendered should be = 8 , no matter he out late the rendered will still 8 hours, unless he timeout early or timein late the rendered will be minus.

    I have 3 shifts, 21:35:00- 05:35:00 which is night shift, and morning shift 05:35:00 - 13:35:00, 13:35:00 - 21:35:00

    Thank you so much


  •  

    Posting Permissions

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