Between dates select question

[Ward]

Hi again,

MySQL ver 4.0.25

I'm still playing with a rental calendar that I was originally using just to display booked dates etc, then I had the notion that I could use the same calendar to display prices etc. and also add a search to.

I've looked at a few solutions on this forum, but they only enter booked dates into the table and not those that are available.

My table is basically this:
tbl_availability
----------------------------------------------
id(int11) | status(int1) | ddate(date) | price(int4)
----------------------------------------------
45 | 1 | 2006-02-01 | 250
45 | 1 | 2006-02-02 | 250
45 | 1 | 2006-02-03 | 250
45 | 1 | 2006-02-04 | 300
45 | 5 | 2006-02-05 | 300
45 | 3 | 2006-02-06 | 300
45 | 3 | 2006-02-07 | 300
45 | 5 | 2006-02-08 | 300

I enter a status code of 3 for booked with a code of 5 for changeover days(a quick fix due to not being able to do subqueries on MySQL ver), status code 1 is available.

The search uses an $arrival_date and $dep_date and I am stuck on part of the query.

In long hand I want to be able to select property_id from tbl_availability where ALL ddates between $arrival_date and $dep_date have a value of 1 or 5. Thereby removing any property that has a 3 within those dates from the results. Can this be done without a subquery and if so what logic should I apply?

[longneck]

select property_id
     , count(*) - sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail
  from tbl_availability
 where ddate between '$arrival_date' and '$dep_date'
group
    by property_id
having unavail = 0

[Ward]

Thanks Longneck, have you time to explain the

count(*) - sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail

I take it this will filter through the results to find dates where the status is unavailable and then the last part of the query removes them from the results?

[longneck]

not quite. the where clause selects all of the rows in that date range, regardless of the status. the qhole query is grouped by the property_id. then the count(*)... line counts the number of rows for that property, then i subtract out the number of rows with a status of 1 or 5. finally, the having clause removes all rows where the property has no unavailable dats in that range.

if that didn't make sense, run this query instead. compare the query to the one i posted above to see what i changed. i think that will help you figure out what's going on:

select property_id
     , count(*) as avail
     , sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail
  from tbl_availability
 where ddate between '$arrival_date' and '$dep_date'
group
    by property_id

[Ward]

Your explanation make total sense, I'm going to sit down and spend some time going over the queries so that I understand it fully. Thanks for your time.

[longneck]

wow. i completely misunderstood your explaination. you are 100% correct. the first time i read "then the last part of the query removes them from the results?" i thought you were refering to the sum(case...) part of the line you quoted.

[Ward]

I haven't seen a query like this before, had no need for one until now so understanding how it works will help for future projects. Thanks again.