SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to calculate the total working hours of an employee from a set or records

    hi everybody i am chaitanya. i have a problem, please help me with it.

    i have a table named emp_attendance. it contains emp_id,in_time,out_time.

    emp_id varchar(20)
    att_date date
    in_time timestamp
    out_time timestamp

    the sample details are

    emp_id] att_date in_time out_time
    1C00A5FD44 2009-10-31 2009-10-31 11:30:00 2009-10-31 12:00:00
    1C00A5FD44 2009-10-31 2009-10-31 12:30:00 2009-10-31 13:00:00
    1C00A5FD44 2009-10-31 2009-10-31 13:30:00 2009-10-31 14:00:00
    1C00A5FD44 2009-10-31 2009-10-31 14:30:00 2009-10-31 15:00:00
    1C00A5FD44 2009-10-31 2009-10-31 15:30:00 2009-10-31 16:00:00

    i want the employee total working hours:minutes:seconds for a given date. for example for 2009-10-31 the employee working hours is 02:30:00.

    please help me with this. thank you all in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT emp_id 
         , SEC_TO_TIME(SUM(TIME_TO_SEC(out_time)
                          -TIME_TO_SEC(in_time))) AS working_hours
      FROM emp_attendance
    GROUP
        BY emp_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: thank you very much sir

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: 1C00A5FD44 2009-10-31 2009-10-31 15:30:00 2009-01-11 15:30:00

    sir, if the in_time and out_time are two different dates the query is not showing the time difference.

    for example if the employee logged in on 2009-10-31 15:30:00 and logged out on 2009-11-01 15:30:00, then the time difference is 24:00:00. but this query is showing 00:00:00.

    please help me sir.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT emp_id 
         , CONCAT(
           CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) > 86400
                THEN CONCAT('',FLOOR(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) / 86400 )
                              , ' days, ' )
                ELSE '' END
            , SEC_TO_TIME(MOD(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) , 86400 )
                 ) ) AS working_hours
      FROM emp_attendance
    GROUP
        BY emp_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r973: sir, i am sorry to say. this query is not working. my operating system is windows xp. please help me.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by krishnaanu View Post
    please help me.
    sure

    please provide a dump of some data -- the CREATE TABLE and INSERT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: sit i am currently doin a project, which tracks the employee attendance using a RFID module. when ever an employee swipes his card, if he is in then the out time is noted, if he is out then the in coming time will be noted.

    based on the employee attendance at the end of the month his salary will be calculated. salary will be calculated on hourly basis. so I need to know the exact worked hours for a particular employee.

    i calculated the worked hours with java coding. but i thought that it will not be fine.... java calculating the hours...it is not fine. it will become an extra burden for the application. so i decided to do it in the database level. please help me sir.

    sir this is the sample data and the table structure.

    Code:
    create table emp_attendance(trans_id bigint primary key not null,
                                             emp_card_id varchar(20) not null,
                                             att_date date not null,
                                             in_time timestamp not null,
                                             out_time timestamp null,
                                             status smallint not null,
    foreign key(emp_card_id) references emp_details(emp_card_id))
    ;

    if employee is entering:
    Code:
    insert into emp_attendance values(1,<<emp_card_id>>,current_date,now(),null,0);
    if employee is leaving:
    Code:
    update emp_attendance set out_time=now() where emp_card_id=<<emp_card_id>> and out_time is null and att_date=current_date;

    sample data:

    Code:
    trans_id   emp_card_id      in_time                       out_time                      status
    1            1C00EDF12D      2009-11-03 10:30:21    2009-11-03 12:30:31     0
    2            1C00EDF12D      2009-11-03 13:40:00    2009-11-03 13:50:00     0
    3            1C00EDF12D      2009-11-03 14:10:00    2009-11-03 14:25:00     0
    4            1C00EDF12D      2009-11-03 14:30:21    2009-11-03 14:40:31     0
    5            1C00EDF12D      2009-11-03 16:30:21    2009-11-03 17:30:31     0
    the difference in the 1st record is 02:00:10
    the difference in the 2nd record is 00:10:00
    the difference in the 34d record is 00:15:00
    the difference in the 4th record is 00:10:10
    the difference in the 5th record is 01:00:10

    finally the employee worked 03:35:30(three hours 35 minutes 30 seconds. i want the output like this.

    please help me sir. waiting for your reply. thank you in advance sir.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i used your sample data, and my first query worked fine --
    Code:
    CREATE TABLE emp_attendance
    ( trans_id BIGINT PRIMARY KEY NOT NULL
    , emp_card_id VARCHAR(20) NOT NULL
    , att_date DATE NOT NULL
    , in_time TIMESTAMP NOT NULL
    , out_time TIMESTAMP NULL
    , status SMALLINT NOT NULL
    );
    INSERT INTO emp_attendance 
    ( trans_id,emp_card_id,in_time,out_time,status)
    VALUES
     (1,'1C00EDF12D','2009-11-03 10:30:21','2009-11-03 12:30:31',0)
    ,(2,'1C00EDF12D','2009-11-03 13:40:00','2009-11-03 13:50:00',0)
    ,(3,'1C00EDF12D','2009-11-03 14:10:00','2009-11-03 14:25:00',0)
    ,(4,'1C00EDF12D','2009-11-03 14:30:21','2009-11-03 14:40:31',0)
    ,(5,'1C00EDF12D','2009-11-03 16:30:21','2009-11-03 17:30:31',0)
    ;
    SELECT emp_card_id 
         , SEC_TO_TIME(SUM(TIME_TO_SEC(out_time)
                          -TIME_TO_SEC(in_time))) AS working_hours
      FROM emp_attendance
    GROUP
        BY emp_card_id;
    /* results  --------
    emp_card_id  working_hours
    1C00EDF12D    03:35:30
    */
    i modified my second query slightly and it works fine too --
    Code:
    SELECT emp_card_id 
         , CONCAT(CAST(
           CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) > 86400
                THEN FLOOR(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) / 86400 )
                ELSE 0 END AS CHAR)
    	       , ' days' ) AS working_days
            , SEC_TO_TIME(MOD(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) , 86400 )
                 )  AS working_hours
      FROM emp_attendance
    GROUP
        BY emp_card_id;
    /* results --------
    emp_card_id  working_days  working_hours
    1C00EDF12D   0 days        03:35:30
    */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: thank you sir, i will tyr this one and definitely inform u

  11. #11
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: sir, i have executed the query. it is working fine when the in_time and out_time are from the same date.

    but when in_time is '2009-11-03 12:00:12' ans out_time is '2009-11-04 12:00:24', the time difference is 24:00:12(twenty four hours,twelve seconds).

    the query is subtracting only the time portion of timestamp. it is not considering the date part. the query is showing 00:00:12, instead if 24:00:12.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    /* add the 24-hour example */
    
    INSERT INTO emp_attendance 
    ( trans_id,emp_card_id,in_time,out_time,status)
    VALUES
     (22,'1937937937','2009-11-03 12:00:12','2009-11-04 12:00:24',0)
    ;
    
    /* try the query again */
    
    SELECT emp_card_id 
         , CONCAT(CAST(
           CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) > 86400
                THEN FLOOR(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) / 86400 )
                ELSE 0 END AS CHAR)
             , ' days' ) AS working_days
            , SEC_TO_TIME(MOD(
                     SUM(UNIX_TIMESTAMP(out_time)
                        -UNIX_TIMESTAMP(in_time)) , 86400 )
                 )  AS working_hours
      FROM emp_attendance
    GROUP
        BY emp_card_id;
    
    /* results --------
    emp_card_id  working_days  working_hours
    1937937937   1 days        00:00:12
    1C00EDF12D   0 days        03:35:30
    
    */
    now if you feel you need to convert 1 day + 00:00:12 into 24:00:12, then you will have to do this in your front end language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937: thank you very much sir for all your help. thank u thank u thank u veryyyy much.


Bookmarks

Posting Permissions

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