Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006

    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. #2
    Join Date
    Jul 2004
    Southampton, UK
    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).

    SELECT ActivityName, DateValue([ActivityStart]) AS ActivityDate, IIf(DateValue([ActivityStart])=DateValue([ActivityStop]),[ActivityStop]-[ActivityStart],(1-TimeValue([ActivityStart]))) AS ActivityTime
    FROM Activity
    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.


Posting Permissions

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