SQL table Date Field Comparision

Question

I have two table for my book reservation form. One table has book number(1-10) and status; all the status is 'available' at first.

Second table has name of the reserver and date field (check-in, check-out) and both table connected with a foreign key. So that table1 to table2 has one-to-many relation; means 1 book can reserved by many students.

Problem:

When I get a reserve request for lets suppose book1 ; then the status of that book1 should be change to 'Oh Hold' for that date time(on table1). When second request come for the same book1, on the same date range or overlap the date, it must tell some message e.g. this date is already reserved or like that, so that second requester can not reserve on the same date for the same book but he can request another book.

How can I check the already exist sql table date to current entering date ??

Highly appreciated your reply.

Thanks,

Answer 1

Basically you will be looking this date patterns:

• the first date can't be in the reserved range.
• the last date can't either be in the reserved range
• if the first date less than reserved first date, the last date can't be greater than the reserved range last date:

Something like this will find current reservations:

SELECT *
FROM reservations 
WHERE book = @bookId
AND (
(@dat_ini BETWEEN begin_date AND end_date)
OR (@dat_end BETWEEN begin_date AND end_date)
OR (@dat_ini < begin_date AND @dat_end > end_date)
)