# Thread: Help with Summing Time Durations

1. Registered User
Join Date
Apr 2006
Posts
5

## Unanswered: Help with Summing Time Durations

I need to calculate daily durations (in minutes), using calendar days, of activities.

What I have are 3 relevant fields on a single table:

ActivityStart (General Date, e.g. 3/4/2006 8:00 PM)
ActivityStop (General Date, e.g. 3/4/2006 9:32 PM)
ActivityName (text)

The problem is we want to sum the duration of activities for each day, which means if an activity crosses 12am (midnight) it's split.

For example:

ActivityStart: 3/4/2006 10:11 PM
ActivityStop: 3/5/2006 1:41 AM

Is 1 row on the table but in the query (or report) should be 2.

The above example (assuming there were no other activities) should appear as:

Sum of 3/4/2006: 109 minutes
Sum of 3/5/2006: 101 minutes

Any one have any ideas?
Last edited by David216; 08-25-06 at 15:52.

2. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Have a go with this. It's two queries stuck together with a UNION. The first part works out how much of the activity falls into the current date for each record and the second part creates the records for when records fall into the next day (I've made a big assumption that there will only ever be one day overlap).

Code:
```SELECT ActivityName, DateValue([ActivityStart]) AS ActivityDate, IIf(DateValue([ActivityStart])=DateValue([ActivityStop]),[ActivityStop]-[ActivityStart],(1-TimeValue([ActivityStart]))) AS ActivityTime
FROM Activity
UNION
SELECT ActivityName, DateValue([ActivityStop]) AS ActivityDate, TimeValue([ActivityStop]) AS ActivityTime
FROM Activity
WHERE DateValue([ActivityStart])<>DateValue([ActivityStop])```
Let me know if you are struggling with it and I'll send an example.

hth
Chris

#### Posting Permissions

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