SQL get IDs that are available in the given time interval for X consecutive days

Question

In an SQL table I keep bookings for various resouces, with a StartDate/EndDate column:

ResourceID,  StartDate,   EndDate
-----------------------------------
1          2009-01-01    2009-01-05
1          2009-01-07    2009-01-10
2          2009-01-03    2009-01-18

I need to produce a list of all resources that are available for at least X consecutive days in a given time interval: ie from 2009-01-01 to 2009-01-20 and X= 5 consecutive days.

eg. for ResourceID=1 ,the biggest available time interval is 2009-01-10 =>2009-01-20 so this qualifies, but for ResourceID=2 there is no 5-days available slot. there is an available one from 2009-01-01 => 2009-01-03 and another at 2009-01-18=> 2009-01-20 , neither long enough

EDIT:

Based on the answer from Quassnoi, I've added some modifications to handle some edge cases: resources w/out reservations, or reservations that span over the entire lookup period.

This looks like the final result. Muchos gracias for all the help !

WITH   avRows AS        (         
     SELECT  a.ID as aid,startDate , endDate , ROW_NUMBER() OVER (PARTITION BY ResourceID ORDER BY endDate ) AS rn   
     FROM    tblResources a left outer join tblReservations  b          
     on b.ResourceID = a.ID and (startDate  BETWEEN '2009-01-01' AND '2009-01-20'  OR endDate  BETWEEN '2009-01-01' AND '2009-01-20' )
     where a.ID NOT IN (select distinct ResourceID from tblReservations where    (startDate  <'2009-01-01'   AND  endDate  > '2009-01-20' ))
     )SELECT  DISTINCT COALESCE(rs.aid, rp.aid)

      FROM   avRows rs FULL JOIN    avRows rp ON   rs.aid = rp.aid  AND rp.rn = rs.rn - 1 
      WHERE   DATEDIFF(day, COALESCE(rp.endDate , '2009-01-01'), COALESCE(rs.startDate , '2009-01-20')) >= 5

Answer 1

SELECT  resourceID
FROM    mytable
WHERE   startDate BETWEEN '2009-01-01' AND '2009-01-20'
        AND DATEDIFF(day, CASE WHEN endDate < '2009-01-20' THEN endDate ELSE '2009-01-20' END, startDate) >= 5
UNION
SELECT  resourceID
FROM    mytable
WHERE   endDate BETWEEN '2009-01-01' AND '2009-01-20'
        AND DATEDIFF(day, endDate, CASE WHEN startDate > '2009-01-01' THEN startDate ELSE '2009-01-01' END) >= 5

Update:

Sorry, didn't notice your records mean busy resources, not free resources.

Try this:

WITH    rows AS
        (
        SELECT  ResourceID, StartDate, EndDate, ROW_NUMBER() OVER (PARTITION BY ResourceID ORDER BY EndDate) AS rn
        FROM    mytable
        WHERE   StartDate BETWEEN '2009-01-01' AND '2009-01-20'
                AND EndDate BETWEEN '2009-01-01' AND '2009-01-20'
        )
SELECT  DISTINCT COALESCE(rs.ResourceID, rp.ResourceID)
FROM    rows rs
FULL JOIN
        rows rp
ON      rs.ResourceID = rp.ResourceID
        AND rp.rn = rs.rn - 1
WHERE   DATEDIFF(day, COALESCE(rp.EndDate, '2009-01-01'), COALESCE(rs.StartDate, '2009-01-20')) >= 5

Update 2:

More detailed explanations and performance tests:

• Inverting date ranges (works in SQL Server, Oracle and PostgreSQL 8.4)
• Inverting date ranges: MySQL (works in MySQL)