Counting multiple overlapping records in the same table

[choaspes]

Dear All

I have a table in which each record has a start date and an end date. The following query appears successfully to count the number of occasions on which one record within the table overlaps with another:

SELECT Count(1)
FROM tblMain AS t1 INNER JOIN tblMain AS t2 ON [t1].[id] < [t2].[id]
WHERE NOT ([t1]![startdate] < [t2]![startdate] And [t1]![enddate] < [t2]![enddate] Or [t1]![startdate] > [t2]![startdate] And [t1]![enddate] > [t2]![enddate]);

What I am failing to discern is how to adapt this query to return the number of occasions on which one record in the table overlaps with TWO others.

Ideally what I'd like is a query in which column one returns the number of occasions two records overlap, column two returns the number of occasions on which three records overlap, column three returns the number of occasions on which four records overlap....and so on and so on.

It feels like it should be eminently possible but I have been comprehensively defeated and would be very grateful for some help!

 

[plog]

Possible, but not efficient. You are going to need to compare every record of tblMain against every record in tblMain. This could take a while if there's a lot of data in there. This is how I would do it:

Make a function to see if your 2 ranges overlap:

Public Function has_Overlap(in_start1 As Date, in_end1 As Date, in_start2 As Date, in_end2 As Date) As Integer
    ' sees if first date range (in_start1, in_end1) overlaps at all with 2nd date range (in_start2, in_end2)
    
    ret = 0         ' return value, 0=no overlap, 1=overlap
     
    If (in_start1 <= in_start2) And (in_start2 <= in_end1) Then ret = 1
  ' range 1 starts first, but range 2 starts before it ends
  
    If (in_start2 <= in_start1) And (in_start1 <= in_end2) Then ret = 1
  ' range 2 starts first, but range 1 starts before it ends

    has_Overlap = ret

End Function

You pass it 4 dates, it returns 1 if they overlap, and 0 if they don't.

Then you use this SQL for your query:

SELECT tblMain.ID, tblMain.startdate, tblMain.enddate, Sum(has_Overlap([tblMain].[startdate],[tblMain].[enddate],[tblMain_1].[startdate],[tblMain_1].[enddate]))-1 AS [Overlaps]
FROM tblMain, tblMain AS tblMain_1
GROUP BY tblMain.ID, tblMain.startdate, tblMain.enddate;

 

[choaspes]

Dear Plog

Thank you very much for your reply! I've learned a huge amount from this method, it's been extremely helpful and I can see this being a very fruitful avenue for me to explore for various purposes in the future.

With respect to my current task though I've already hit another wall. The background to this is that I want to be able to regularly analyse demand/capacity, each record represents a session that requires a room. In order to ascertain whether we currently have enough rooms we need to know how often we're using multiple rooms at the same time - so we're looking for the number of occasions on which two rooms were concurrently is use / three rooms were concurrently in use / four rooms were concurrently in use.

The current method will tell me, for example, that one particular session happened to overlap with three other sessions - but it won't tell me how many rooms were required in total to accommodate all of those sessions. The other three sessions might have run concurrently (meaning I'd need four rooms in total) or they might have run consecutively within the currency of the first (meaning I'd only need two rooms).

Is there a way this method can be adapted to return figures to inform this kind of analysis?

 

[plog]

Now it sounds like you want a list of dates along with how many tblMain records occupy each date. It is possible, but you are going to need a data source of dates that you want to report on.

Suppose you wanted to report on January 2015, you would need a table or query that provided you with this data:


ReportDate
1/1/2015
1/2/2015
1/3/2015
...
1/31/2015

With that you could then generate the data you want. Do you have a data source like that? And is that what you truly want? You've already moved the goal posts on me once, its probably best you tell me exactly what you are trying to achieve.

 

[choaspes]

Sorry Plog. I sense your annoyance, it certainly hasn't been my intention to move goalposts, I think I'm just struggling to explain.

I have used the same word "occasions" in both posts very deliberately because it doesn't denote a defined interval of time. This is because returning the number of overlapping records over any given fixed interval won't necessarily return a useful answer for the purposes of capacity planning.

On any given month/week/day you could have 100 records that overlap with another record at some point during that week/month/day, but returning the number 100 won't tell me how many of those records were concurrent at any given point during that period - so I won't know the minimum number of "rooms" I needed in order to accommodate the peak concurrent demand.

It could be that those 100 records were spaced so that only two were ever simultaneously concurrent - in which case I only need two rooms. Conversely it could be that on one "occasion" during that day (be that for 5 seconds or 5 hours) we had twenty concurrent records - in which case I needed twenty rooms.

In practice the intervals in the table are defined to the nearest minute and always between 1 and 72 hours. Perhaps I just haven't thought this through properly and my whole approach is wrong. Sorry if I've led you on a wild goose chase.

 

[plog]

Not a wild goose chase, but to expand on my point before: To do this with a query, you need a datasource that identifies the most granular level you want to report on. If there can be an overlap of just 1 minute, then you need a datasource that allows you to report on every minute:

ReportMinutes
1/1/2015 00:00
1/1/2015 00:01
1/1/2015 00:02
...
1/31/2015 23:59

The more granular you go, the longer this thing is going to take to process.

This could also be solved via VBA and recordsets. You might post over in that section for ideas, but that too will take a while to compute.

 

[choaspes]

Thanks Plog. I suppose it's going to have to be minutes! In practice I would only really need this level of granularity when 3 or more records are concurrent, which is going to be very rare. Does that help with processing time at all?

 

[plog]

Not really, what we are trying to find out is concurrency. You can't apply criteria to something until you've determined it.

 

[choaspes]

Of course...