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 10 of 10
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Problem in calculating time difference from one column with datatype datetime

    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
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The 100+ responses in two threads in another forum not enough to get you started?

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    The 100+ responses in two threads in another forum not enough to get you started?
    Sorry, I really don't know how can I solved my problem. that's why I post a lot of threads.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Yes, but when you post in multiple forums and people find out you are doing it, then you stop getting answers. It is called CROSS POSTING and it is very very bad "Netiquette."

    I, for one, will not answer a question in *EITHER* forum if I find out it is cross posted.

    Your problem here is a very easy one. It's hard for me to believe that it needed more then ONE answer. Why did you get hundreds?
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    By the way, the output you say you want is *USELESS*.

    Say you *did* get output that looked like this:
    Code:
    EMP_NO TotalHours
    300395 08:00:00
    300395 07:00:00
    300395 08:00:00
    300395 07:00:00
    300395 08:00:00
    300395 05:00:00
    300395 08:00:00
    300395 03:30:00
    300395 01:10:00
    300395 08:00:00
    300395 08:00:00
    300395 04:12:00
    HOW CAN YOU TELL which date goes with WHICH number of hours?????

    What is the point of getting data that is as useless as that?
    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.

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Sorry :(

    I follow only what the instruction by my boss..

    I post a lot of threads because its an urgent..

    Thank you

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    yes but that is causing other people (whom you are not paying) to start from scratch when you have been given a lot of guidance already on the subject.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    And we can't help it if you boss is an idiot. Or if you don't understand what he is saying, whichever it is.

    I don't pretend to understand why you are inserting this data into another table. That's silly. Just create a VIEW that you can use over and over.

    Code:
    CREATE VIEW timeWorkedByEmployeeByDate 
    AS
    SELECT EMP_NO, `DATE`, TIMEDIFF( MAX(DTR), MIN(DTR))
    FROM regular_dtr
    GROUP BY EMP_NO, `DATE`
    ORDER BY EMP_NO, `DATE`
    Presto.

    Go on, try it.

    Then you can use [icode]SELECT * FROM timeWorkedByEmployeeByDate[/code] any place you would have use the silly regular_dtr_total before.
    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-24-2011)

  • #9
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And we can't help it if you boss is an idiot. Or if you don't understand what he is saying, whichever it is.

    I don't pretend to understand why you are inserting this data into another table. That's silly. Just create a VIEW that you can use over and over.

    Code:
    CREATE VIEW timeWorkedByEmployeeByDate 
    AS
    SELECT EMP_NO, `DATE`, TIMEDIFF( MAX(DTR), MIN(DTR))
    FROM regular_dtr
    GROUP BY EMP_NO, `DATE`
    ORDER BY EMP_NO, `DATE`
    Presto.

    Go on, try it.

    Then you can use [icode]SELECT * FROM timeWorkedByEmployeeByDate[/code] any place you would have use the silly regular_dtr_total before.
    Okay..

    I will try it..

    Thank you so much...

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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
    •