time intervals and durations are notoriously difficult to handle in sql
what you want is to know that there is an "availability" on
each day in the range
you
could do it with an EXISTS predicate on each date within the interval, but this means hardcoding them all, and it could get repetitive and tedious:
Code:
select 'yes' as roomisfree
from timetable
where room = 1
and datefld = '2004-02-01' -- hardcode first date
and availability = 'yes'
and exists
( select 1
from timetable
where room = 1
and datefld = '2004-02-02' -- hardcode second date
and availability = 'yes'
)
and exists
( select 1
from timetable
where room = 1
and datefld = '2004-02-03' -- hardcode third date
and availability = 'yes'
)
note that if all the conditions are met, you will get back one row with one column called roomisfree, otherwise you will get 0 rows back
the same results can be obtained like this:
Code:
select availability as roomisfree
from timetable
where room = 1
and datefld between '2004-02-01' -- hardcode first date
and '2004-02-03' -- hardcode last date
and availability = 'yes'
group
by availability
having count(*) = 3 -- number of dates
here you need code only the first and last dates in the interval, as well as the number of dates the range covers
dBforums
>
Using multiple ANDs in a statement
02-15-04, 09:13
Linear Mode
