I have the following set of dates (dd/MM/yyyy) matching events in my database:
eventId startDate endDate 1 02/05/2009 10/05/2009 2 08/05/2009 12/05/2009 3 10/05/2009 12/05/2009 4 21/05/2009 21/05/2009 5 25/05/2009 NULL 6 01/06/2009 03/06/2009
The events have a start and end date (times don't matter) and a NULL endDate means that the event is still in progress.
What I would like to determine is the ranges of dates between two arbitrary dates where there was a) no event and b) the events were overlapping.
So for an input date range of 01/04/2009 - 30/06/2009 I would expect to have the following results:
no event: 01/04/2009 - 01/05/2009 overlap : 08/05/2009 - 10/05/2009 overlap : 10/05/2009 - 12/05/2009 no event: 13/05/2009 - 20/05/2009 no event: 22/05/2009 - 24/05/2009 overlap : 01/06/2009 - 03/06/2009
Note that the two adjacent overlap ranges would be acceptable as one result.
Can anyone please help me with a SQL algorithm to generate this result set?
EDIT: The target platform database is SQL Server 2005. The dates are recorded as 10/05/2009 00:00:00, meaning that the event ended some time between 10/5/2009 00:00:00 and 10/5/2009 23:59:59. The same is true for the start dates. The input date range therefore also can be read as 01/04/2009 00:00:00 - 30/06/2009 23:59:59.