Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Help with subtracting data from multiple rows

    I am needing to find the difference in time between multiple rows of data. The data represents a persons clock in and clock out events. I want to compile all of the data for a certain persons events for a single day and determine the amount of time that was on break.

    The data that is available is
    PersonFullName
    PersonNum
    WorkDay
    InTime
    OutTime

    Here is a sample of the data
    Test1, 1, 4/2/2011, 8:00, 12:00
    Test1, 1, 4/2/2011, 13:00, 17:00

    In this case I would like to return 60 minutes or the difference between 13:00 - 12:00

    Here is a more complex and frequent example
    Test2, 2, 4/2/2011, 7:30, 11:30
    Test2, 2, 4/2/2011, 12:15, 15:30
    Test2, 2, 4/2/2011, 16:00, 17:30

    In this case I would like to return 75 minutes or sum of differences between (12:15 - 11:30) + (16:00 - 15:30)

    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT Table1.PersonFullName, Table1.PersonNum, Table1.WorkDay, Sum(DateDiff("n",[InTime],[OutTime])) AS Difference
    FROM Table1
    GROUP BY Table1.PersonFullName, Table1.PersonNum, Table1.WorkDay
    ORDER BY Table1.PersonFullName, Table1.WorkDay;
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I think that Sinndho's suggestion will give the number of minutes that the person worked each day. You can use that if you also work out the duration between the person's first time in and last time out on a given day. This might work:
    Code:
    SELECT Table1.PersonFullName
    , Table1.PersonNum
    , Table1.WorkDay
    , DateDiff("n", Min([InTime]), Max([OutTime])) AS Total
    FROM Table1
    GROUP BY Table1.PersonFullName
    , Table1.PersonNum
    , Table1.WorkDay;
    If you subtract Difference from Total, you should be left with the total number of break minutes.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    ... and determine the amount of time that was on break.
    Oops ! I missed the ending

    Thanks weejas !
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    2

    Thanks

    Thanks for the great code. I will give it a test.

    Steve

Tags for this Thread

Posting Permissions

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