Re: checking item availability by date

[Puiu Hrenciuc]

So if I'm understanding corectly:

Table 1 ( item_table ) Table 2 (hired_items)
--------------------- --------------------
item_id 1 --------------------------< item_id
item_name hired_start
item_description hired_end
-- other data ----

That's what I understand from what you said : one table with items ( item_no is a primary key ) and a second table with hired_items . One item in the first table can
be in the second table once, many times or not at all. You want to see if a given
item ( let's name it item_test ) is available in a given period (start_date and end_date)

SELECT COUNT(item_id)
FROM hired_items
WHERE item_id='ITEM_ID_TO_CHECK' AND (
(hired_start<='entered_start_date' AND hired_end>='entered_start_date') OR
(hired_start<='entered_end_date' AND hired_end>='entered_end_date') OR
(hired_start>'entered_start_date' AND hired_end<'entered_end_date')
)

this statement will check if the given item (ITEM_ID_TO_CHECK) is hired in the given
period (entered_start_date, entered_end_date ) and will return the number of records it intersects. So any result beside 0 means that the item is not available and a 0
result means that the item is available. Maybe you can optimize this SQL in some way
or use it as a subquery. Anyway I think this statement solves your problem since if
the item is not in the hired_table it will return 0 (so it is OK ).