Archive for November 9th, 2009
From Stack Overflow:
In an SQL table I keep bookings for various resouces, with a
ResourceID StartDate EndDate 1 2009-01-01 2009-01-05 1 2009-01-07 2009-01-10 2 2009-01-03 2009-01-18
A record means that the given resource is busy.
I need to produce a list of all resources that are available for at least X consecutive days in a given time interval, e. g. from 2009-01-01 to 2009-01-20 and 5 consecutive days.
This would be quite an easy task if the table records reflected available resources. We could just check that a resource exists using
EXISTS and that it has given length using
However, the table reflects the busy resources. This means we should invert the date ranges: given a list of consecutive non-overlapping date ranges, we should build a list of their complements, or the gaps between them.
Here’s how we do it.
First, we should build an ordered set of the date ranges. Since the ranges are not overlapping, the set can be ordered by any column (
EndDate), with the result being the same: