Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate overlapping times (97-SR2)

    I am stumped how to approach this problem. Can anybody shed some light:

    Given a table of the following structure (supplied by an automated data-logger)
    ActivityCode
    StartTime
    EndTime

    and the following simplified sample records
    1 1:00 pm 2:00 pm
    2 1:30 pm 2:30 pm

    How would I construct a query or create a new table that shows the intersection of the two records i.e. calculate that activities 1 and 2 both occurred between 1:30 and 2:00 pm? Can it be done with SQL, or would a brute-force approach of walking thru all the records be required? The real dataset will contain thousands of records with many different activities. Once I get the solution for intersecting two activities, I would be interested in finding the intersection of three or more activies. Would probably also look for the intersection of one activity and NOT the other activity.

    Not looking for the complete solution -- just some guidance in the right direction, or if it's even possible.

    Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculate overlapping times (97-SR2)

    Try using Min and Max of the Start and End times for a range of activities.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calculate overlapping times (97-SR2)

    Are you interested in using the query to find out what was happening over a selected time period ?
    In that case build a parameter query that selects all records where starttime <= starttimeparameter and
    finishtime >=endtimeparameter.
    Presumably you would also have a date criterion as well.
    I would put the two times and the date on a form.
    Regards
    John



  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calculate overlapping times (97-SR2)

    Some example of queries using some ideas previously suggested:

    This queries selects all ActivityCodes whose interval falls between two specified Start and End times (parameter query):

    <pre>PARAMETERS [Enter Start Time:] DateTime, [Enter End Time:] DateTime;
    SELECT tblTime.ActivityCode, tblTime.StartTime, tblTime.EndTime
    FROM tblTime
    WHERE (((tblTime.StartTime) Between [Enter Start Time:]
    And [Enter End Time:])) OR (((tblTime.EndTime) Between
    Enter Start Time:] And [Enter End Time:]))
    ORDER BY tblTime.ActivityCode;
    </pre>


    This query calculates the overlapping time interval ("Overlap_Start" and "Overlap_End") for three specified Activity Codes:
    <pre>SELECT Max(tblTime.StartTime) AS Overlap_Start, Min(tblTime.EndTime)
    AS Overlap_End
    FROM tblTime
    WHERE (((tblTime.ActivityCode)="A" Or (tblTime.ActivityCode)="B"
    Or (tblTime.ActivityCode)="C"))
    HAVING (((Min(tblTime.EndTime))>=Max([StartTime])));</pre>

    You can specify any number of Activity Codes in query criteria, I used three for demonstration purposes; modify query as required. I used letters for Activity Codes. If there is no overlapping time interval for specified Activity Codes, query will return no records. If there is overlap one record will be returned with overlap start and end values. The HAVING clause uses >= operator so if one ActivityCode starts when another ends you will get result where overlap start = overlap end.

    HTH or gives you some ideas to get started.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate overlapping times (97-SR2)

    Thanks for the input, but I wasn't as clear in my original posting as I should have been. Specifying the start and end time is not feasible -- I want to find any occurrence of overlap times, not just predetermined times.

    I figured it out by using querying the table, and then joining to itself. First, I found all the activties of a particular code via a query named qryBaseActivity, and then I found all the activities of a second code where its starting time was between the start and stop times of the Base Activities returned by the first query. In SQL (hard-wired for now), it looks like:

    qryBaseActivity:
    SELECT * FROM qry1AllIntervals WHERE (((Channel)=8) AND ((Code)=0));


    qryBaseIntersectSpecifiedActivity:
    SELECT qry1AllIntervals.*, q.StartTime AS BaseStart, q.StopTime AS BaseStop
    FROM qry1AllIntervals, qryBaseActivity as q
    WHERE (((qry1AllIntervals.Channel)=0) AND ((qry1AllIntervals.StartTime)>[q].[starttime] And (qry1AllIntervals.StartTime)<=[q].[stoptime]));


    Next, I used a nifty technique gleaned from Ken Getz' column in Office Advisor magazine. You can replace a stored query used in a second SQL statement by its SQL statement if you enclose it in square brackets and append a "." This reduces everything to a single SQL statement:

    SELECT qry1AllIntervals.*, q.StartTime AS BaseStart, q.StopTime AS BaseStop
    FROM qry1AllIntervals, [SELECT * FROM qry1AllIntervals WHERE (((Channel)=8) AND ((Code)=11))]. AS q
    WHERE (((qry1AllIntervals.Channel)=0) AND ((qry1AllIntervals.StartTime)>[q].[starttime] And (qry1AllIntervals.StartTime)<=[q].[stoptime]));


    In this particular example, I can change the "Code = 11" to any valid Code, and the query will return all the records where the two activities intersect. I tried using the "PARAMETERS" clause as you illustrated, but haven't been successful so far. I'll have another try at it, or just build the SQL on the fly. I will have to refine the query to return the proper Stop time as you suggested.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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