Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Reply to post Problem in calculatin​g time difference from one column with datatype d

    Good day!

    I got a problem in my query syntax to get the total hours per day based on their time in and time out...

    Here is the scenario:

    the employee sometimes they swipe their id twice to Time In or to Time OUT, so I used syntax max in timeout and min in time in.

    Here is the sample DTR data from the database:
    EMP_NO DATE DTR
    300395 11/3/2011 11/3/11 5:35 AM
    300395 11/3/2011 11/3/11 1:35 PM
    300395 11/4/2011 11/4/11 5:35 AM
    300395 11/4/2011 11/4/11 1:35 PM
    300395 11/5/2011 11/5/11 5:35 AM
    300395 11/5/2011 11/5/11 1:35 PM
    300395 11/6/2011 11/6/11 5:35 AM
    300395 11/6/2011 11/6/11 1:35 PM
    300395 11/7/2011 11/7/11 5:35 AM
    300395 11/7/2011 11/7/11 1:35 PM
    300395 11/8/2011 11/8/11 5:35 PM
    300395 11/8/2011 11/8/11 1:35 PM
    300395 11/9/2011 11/9/11 5:35 PM
    300395 11/9/2011 11/9/11 1:35 PM
    300395 11/10/2011 11/10/11 5:35 AM
    300395 11/10/2011 11/10/11 1:35 PM
    300395 11/11/2011 11/11/11 5:35 AM
    300395 11/11/2011 11/11/11 1:35 PM
    300395 11/12/2011 11/12/11 5:35 AM
    300395 11/12/2011 11/12/11 1:35 PM
    300395 11/14/2011 11/14/11 5:35 AM
    300395 11/14/2011 11/14/11 1:35 PM
    300395 1/15/2011 11/15/11 5:35 AM
    300395 11/15/2011 11/15/11 1:35 PM
    9300127 11/3/2011 11/3/11 5:35 AM
    9300127 11/3/2011 11/3/11 1:35 PM
    9300127 11/4/2011 11/4/11 5:35 AM
    9300127 11/4/2011 11/4/11 1:35 PM
    9300127 11/5/2011 11/5/11 5:35 AM
    9300127 11/5/2011 11/5/11 1:35 PM
    9300127 11/6/2011 11/6/11 5:35 AM
    9300127 11/6/2011 11/6/11 1:35 PM
    9300127 11/7/2011 11/7/11 5:35 AM
    9300127 11/7/2011 11/7/11 1:35 PM
    9300127 11/8/2011 11/8/11 5:35 PM
    9300127 11/8/2011 11/8/11 1:35 PM
    9300127 11/9/2011 11/9/11 5:35 PM
    9300127 11/9/2011 11/9/11 1:35 PM
    9300127 11/10/2011 11/10/11 5:35 AM
    9300127 11/10/2011 11/10/11 1:35 PM
    9300127 11/11/2011 11/11/11 5:35 AM
    9300127 11/11/2011 11/11/11 1:35 PM
    9300127 11/12/2011 11/12/11 5:35 AM
    9300127 11/12/2011 11/12/11 1:35 PM
    9300127 11/14/2011 11/14/11 5:35 AM
    9300127 11/14/2011 11/14/11 1:35 PM
    9300127 1/15/2011 11/15/11 5:35 AM
    9300127 11/15/2011 11/15/11 1:35 PM


    I used this syntax to get the timedifference per day/employee:
    Code:
    INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
    FROM regular_dtr a
    LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO GROUP BY a.EMP_NO;
    the result in this query is:
    EMP_NO TotalHours
    300395 296:00:00
    9300127 296:00:00

    I want output is:

    EMP_NO TotalHours
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    300395 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00
    9300127 08:00:00


    I search in internet fot the right syntax, i tried time_to_sec, DATEDIFF, sec_to_time, but still wrong input, I post my problem in forum because I need to solved it. And I need help..

    Thank you so much..

  2. #2
    Join Date
    Dec 2010
    Posts
    136
    here is my new code and the data output:
    Code:
    INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
    FROM regular_dtr a
    LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE b.DATE_DTR = a.DATE_DTR AND a.EMP_NO = b.EMP_NO GROUP BY a.DATE_DTR,  a.EMP_NO;
    DTR DATA:
    EMP_NO DATE DTR
    300395 11/3/2011 11/3/11 5:35 AM
    300395 11/3/2011 11/3/11 1:35 PM
    300395 11/4/2011 11/4/11 5:35 AM
    300395 11/4/2011 11/4/11 1:35 PM
    300395 11/5/2011 11/5/11 5:35 AM
    300395 11/5/2011 11/5/11 1:35 PM
    300395 11/6/2011 11/6/11 5:35 AM
    300395 11/6/2011 11/6/11 1:35 PM
    300395 11/7/2011 11/7/11 5:35 AM
    300395 11/7/2011 11/7/11 1:35 PM
    300395 11/8/2011 11/8/11 5:35 AM
    300395 11/8/2011 11/8/11 1:35 PM
    300395 11/9/2011 11/9/11 5:35 AM
    300395 11/9/2011 11/9/11 1:35 PM
    300395 11/10/2011 11/10/11 5:35 AM
    300395 11/10/2011 11/10/11 1:35 PM
    300395 11/11/2011 11/11/11 5:35 AM
    300395 11/11/2011 11/11/11 1:35 PM
    300395 11/12/2011 11/12/11 5:35 AM
    300395 11/12/2011 11/12/11 1:35 PM
    300395 11/14/2011 11/14/11 5:35 AM
    300395 11/14/2011 11/14/11 1:35 PM
    300395 1/15/2011 11/15/11 5:35 AM
    300395 11/15/2011 11/15/11 1:35 PM
    9300127 11/3/2011 11/3/11 5:35 AM
    9300127 11/3/2011 11/3/11 1:35 PM
    9300127 11/4/2011 11/4/11 5:35 AM
    9300127 11/4/2011 11/4/11 1:35 PM
    9300127 11/5/2011 11/5/11 5:35 AM
    9300127 11/5/2011 11/5/11 1:35 PM
    9300127 11/6/2011 11/6/11 5:35 AM
    9300127 11/6/2011 11/6/11 1:35 PM
    9300127 11/7/2011 11/7/11 5:35 AM
    9300127 11/7/2011 11/7/11 1:35 PM
    9300127 11/8/2011 11/8/11 5:35 AM
    9300127 11/8/2011 11/8/11 1:35 PM
    9300127 11/9/2011 11/9/11 5:35 AM
    9300127 11/9/2011 11/9/11 1:35 PM
    9300127 11/10/2011 11/10/11 5:35 AM
    9300127 11/10/2011 11/10/11 1:35 PM
    9300127 11/11/2011 11/11/11 5:35 AM
    9300127 11/11/2011 11/11/11 1:35 PM
    9300127 11/12/2011 11/12/11 5:35 AM
    9300127 11/12/2011 11/12/11 1:35 PM
    9300127 11/14/2011 11/14/11 5:35 AM
    9300127 11/14/2011 11/14/11 1:35 PM
    9300127 1/15/2011 11/15/11 5:35 AM
    9300127 11/15/2011 11/15/11 1:35 PM
    I have 12days for 300395 and also 12 days for 9300127

    And data out in database using this new query is:

    300395 00:00:00
    9300127 00:00:00
    300395 08:00:00
    9300127 08:00:00
    // 11 output like this, it should be 12 output like this and no output 00:00:00
    300395 00:00:00
    9300127 00:00:00

    total output is 26 rows

    it works, but a little bit problem, because, I extra data with 00:00:00 output and also two output become 00:00:00 but it should 08:00:00.

    Actually, the output will be 24 rows only, but it becomes 26 rows and the 4 rows has 00:00:00 output.


    thank you so much

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, sorry, not going to help you over here either

    you've been struggling with this problem for a month, and have made no progress

    the stuff we've tried to teach you, you seem incapable of grasping

    seriously, ask your boss to get you some professional help because this sql is too difficult for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    Now i resolved my problem in total hours per day...

    And now my big problem I need to face is the rendered....Getting only the time between their shifts like 21:35:00 - 05:35:00, 05:35:00 - 13:35:00, and 13:35:00 - 21:35:00...

    Rendered should be if he time in late it will be deduct on his time based on his schedule also if he timeout early...

    I used case statement in my update query but it did not work...I really don't know what should I used syntax to solved my old and new problem.. the rendered:(

    Sorry if until today I did not resolved it...

    Thank you for your help...

Posting Permissions

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