# Thread: Help with subtracting data from multiple rows

1. Registered User
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)

2. Moderator
Join Date
Mar 2009
Posts
5,442
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;```

3. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
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.

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

Thanks weejas !

5. Registered User
Join Date
Apr 2011
Posts
2

## Thanks

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

Steve

#### Posting Permissions

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