How to calculate time overlap

[bacmb]

We have an MS Access DB that has timed records in it. Each record has a start date/time and an end date/time. We need to report on the maximum number of concurrently active records: (i.e. entry 100 started at 10:05 am and ended at 10:15 am; entry 150 started at 10:10AM and ended at 10:20 AM, we have 2 concurrently active records). Can someone point us in the right direction for the methodology to accomplish this in CR?

Thanks!

 

[synapsevampire]

Concurrent for specific periods, or?

Show what you intend as output rather than just what you have.

-k

 

[lbass]

I think you would need to create a table "Alldatetimes" with all possible datetimes in it for the period under consideration. Then link this to the table with the start and end times, by using a left join from {Alldatetimes.datetime} to {yourtable.startdatetime} where the join type is >= and then also create a left join from {Alldatetimes.datetime} to {yourtable.enddatetime} where the join type is <=. Then you could group on {Alldatetimes.datetime} using minute (or the interval you want) as the group interval, and then insert a distinctcount on the ID field.

This solution is probably CR version-dependent.

-LB