Partial availability

Unfortunately doing this with the logic as it currently stands would probably be too much processing overhead. I'm working on a pure SQL version of the logic that fetches the results in a single pass and this would work with what you are suggesting here. I'll leave this as a feature request. In the mean time I'd recommend changing the 'Your search returned 0 results' to 'We were unable to find any rooms matching your search but please give us a call on xxxx xxxx to see if we can make alternate arrangements', I've done this before and the client reports that they get a lot of people who'd rather ring than book online anyway, they just use the site to check availability first. Also, this is better for the property owner because they might save some $ on paypal fees.

In my work on a replacement for uc_hotel (mainly because of lack of Views integration) I had to find a solution for what you described as "single pass SQL", and was successful in doing so.
Now, the main search mechanism in my module is based on View with exposed filter. One field is "vacation length >= available length", and another view is the same, only with "available length-X" for making it flexible...

I see no reason why not to provide my solution to uc_hotel, so larowlan, let me know and I will send you the main concepts and/or code.

My solution was to base the room availability on nodes - I created a new "availability" content type, with date field, a nodereference field to the room type, and a availability number (free rooms).
Having this structure means you can define any complicated view and get your results.

The problem is - how to get vacation length. But this is the same problem in the original uc_hotel query. My solution is to move the complexity to the availability creation/modification, to save the longest vacation length available on every date.
I will try to clarify it with an example:

Let's say this is my starting point:

room name |    date    | availability | length
----------------------------------------------
  momo    | 2010-05-25 |  2 rooms     |  5
  momo    | 2010-05-26 |  2 rooms     |  4
  momo    | 2010-05-27 |  2 rooms     |  3
  momo    | 2010-05-28 |  1 rooms     |  2
  momo    | 2010-05-29 |  2 rooms     |  1

Now someone ordered for 28/5, so I update the table to be:

room name |    date    | availability | length
----------------------------------------------
  momo    | 2010-05-25 |  2 rooms     |  3
  momo    | 2010-05-26 |  2 rooms     |  2
  momo    | 2010-05-27 |  2 rooms     |  1
  momo    | 2010-05-28 |  0 rooms     |  0
  momo    | 2010-05-29 |  2 rooms     |  1

In each point, when I query for "X days starting from date Y", I will get a proper answer in one SQL query==one view...

Anyway, this is my approach, and it seems stable and fit to my needs. In related to partial availablity, you can see that I can make another view, with "length-1", and to get a correct answer as well.

Hope this helps,
Shushu

This works in your query (as a where clause)

 AND nid
IN (SELECT nid FROM (
            SELECT rtid AS nid,
            COUNT(*) AS nights
            FROM {hotel_availability_calendars}
            WHERE caldate >= '%s'
            AND caldate < '%s' /*this has to be less than as they dont stay this night*/
            AND minstay <= %d
            AND minoccupancy <= %d
            AND avail_quantity > 0
            GROUP BY 1) AS nights
         WHERE nights.nights = %d
      ) 
      AND nid NOT IN (
        SELECT rtid
        FROM {hotel_availability_calendars}
        WHERE caldate = '%s'
        AND nocheckin = 1
      ) 
      AND nid NOT IN (
        SELECT rtid
        FROM {hotel_availability_calendars}
        WHERE caldate = '%s'
        AND nocheckout = 1
      )
      AND nid IN (
        SELECT thrt.nid FROM
        {hotel_room_types} thrt INNER JOIN {node} tn
        ON thrt.vid = tn.vid
        WHERE %d BETWEEN min_occ AND capacity
      )

Where your arguments are as follows:
*Check in date
*Check out date
*Number of nights
*Number of guests (adults + children)
*Number of nights **
*Check in date **
*Check out date ** and
*Number of guests (adults + children)
You can modify the ones marked as ** to get partial availability
I'm working on implementing query in the search results this week (replacing the existing logic) - once that is done you should be able to modify it to suit your needs.
This will be as a new branch.