I'm looking for a query to help me solve the following:
- I have a series of events
- Each event has a start and end date.
- Many of these events overlap
- The answer I'm looking for is the maximum number of events that overlap
Say I have 5 events:
- 1 Jan -> 9 Jan
- 7 Jan -> 12 Jan
- 8 Jan -> 10 Jan
- 10 Jan -> 15 Jan
- 12 Jan -> 17 Jan
3 of these events overlap 9th January, which is the maximum overlapping events, so the answer is 3.
(There are also 3 events overlapping on 10th January, but that's the same answer)
What I've Tried
If I was doing this in memory, I could do this:
- For each event:
- Get the start date
- Count the dates that include this event
- Pick the event with the highest count.
But there are 2 issues with this:
- It doesn't appear to be very efficient
- It isn't very SQL-y (ie. is procedural rather than set-based)
How can I implement something like this in SQL?
- I don't care to find the start / end dates of the most overlapping events. I just need a count
- I don't care how often the maximum occurs, I just need the maximum. So, in the example above, I know there are two occasions where 3 events overlap, but I just need the "3".
- If event A ends on the same day as event B starts, they are considered to be overlapping