Calculate overlapping times (97-SR2)

Topic

New Reply

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.

Reply | Quote

Replies

Try using Min and Max of the Start and End times for a range of activities.
Pat

Reply | Quote

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 =endtimeparameter.
Presumably you would also have a date criterion as well.
I would put the two times and the date on a form.

Reply | Quote

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):

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;

This query calculates the overlapping time interval (“Overlap_Start” and “Overlap_End”) for three specified Activity Codes:

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])));

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.

Reply | Quote

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].[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.

Reply | Quote