SQL Table Comparing Date Fields
I have two tables for my booking form. One table has a book number (1-10) and a status; everything is "available" first.
The second table has the field name reserver and date (registration, checkout) and both tables associated with a foreign key. So table1 to table2 has a one-to-many relationship; means that one book can be reserved by many students.
Problem:
When I receive a fallback request, let's assume book1; then the status of this book1 should be changed to "Oh Hold" for this time (on table 1). When the second request comes in for the same book1, in the same date range, or overlaps the date, it should report some message, for example. this date is already reserved or something similar, so the second requester cannot reserve for the same date for the same book, but he can request a different book.
How can I check the already existing date of the sql table to the current input date?
Your answer is highly appreciated.
Thank,
Basically you will be looking for patterns of this date:
- the first date cannot be in the reserved range.
- the last date cannot be in the reserved range
- if the first date is less than the reserved first date, the last date cannot be greater than the reserved range of the last date:
Something like this will find the 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)
)
source to share