Results 1 to 2 of 2

Thread: Adding Hours Worked in 2 Rows

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Question Adding Hours Worked in 2 Rows

    I have a table that has clock in time in one row and clock out time in another row. I need to be able to sum the hours worked in a day.

    TIMESTAMP ETEMP# ETIORO PMNAME
    1/2/09 8:56 AM 213 I John Deere
    1/2/09 1:15 PM 213 O John Deere
    1/2/09 1:43 PM 213 I John Deere
    1/2/09 6:03 PM 213 O John Deere

    I created two queries in access, one with clock in data and one with cloc out data and added the field DateVal for the day. I then created a query with the following SQL language.

    SELECT ClockIn.DateVal, ClockIn.[ETEMP#], ClockIn.PMNAME, ClockIn.ETIORO, ClockOut.ETIORO, ClockIn.TIMESTAMP, ClockOut.TIMESTAMP, DateDiff("n",[clockin].[timestamp],[clockout].[timestamp])/60 AS [Hours]

    FROM ClockIn INNER JOIN ClockOut ON (ClockIn.[ETEMP#]=ClockOut.[ETEMP#]) AND (ClockIn.DateVal=ClockOut.DateVal)

    WHERE (((ClockIn.DateVal)=#1/2/2009#) And ((ClockIn.PMNAME)="John Deere"));

    This is the result I get:

    DateVal ETEMP# PMNAME ClockIn.ETIORO ClockOut.ETIORO ClockIn.TIMESTAMP ClockOut.TIMESTAMP Hours
    1/2/09 213 John Deere I O 1/2/09 1:43 PM 1/2/09 1:15 PM -0.466666667
    1/2/09 213 John Deere I O 1/2/09 8:56 AM 1/2/09 1:15 PM 4.316666667
    1/2/09 213 John Deere I O 1/2/09 1:43 PM 1/2/09 6:03 PM 4.333333333
    1/2/09 213 John Deere I O 1/2/09 8:56 AM 1/2/09 6:03 PM 9.116666667

    How do I eliminate the duplication and get true hours worked for the day. I think it can be done with a subquery, but that is out of my league. Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    trater04, I can't test it but something like this might work:
    Code:
    SELECT col-list
    FROM (SELECT col-list
          FROM table
          WHERE ETIORO = 'I'
         ) AS STR_DATA
       , (SELECT col-list
          FROM table
          WHERE ETIORO = 'O'
         ) AS END_DATA
    WHERE STR_DATA.ETEMP# = END_DATA.ETEMP#
      AND END_DATA.TIMESTAMP = 
             (
              SELECT MIN(TIMESTAMP)
              FROM table END_DATA2 
              WHERE END_DATA2.TIMESTAMP > STR_DATA.TIMESTAMP
                AND END_DATA2.ETIORO = 'O'
             )
    You can add your own COL-LIST columns and the date arithmetic but this should pair the starting IN time with the correct OUT time.

Posting Permissions

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