I have a table hotel_booking
reference | start_date | numberOfDays |
---|---|---|
ABC | 11-08-2021 | 2 |
DEF | 12-08-2021 | 2 |
GHI | 13-08-2021 | 3 |
JKL | 10-08-2021 | 2 |
So the first task I wanted was to get all bookings which has startDate between x and y date which is straight forward query
select reference
from hotel_booking
where DATE(start_date) BETWEEN '2021-08-11' AND '2021-08-12'
This returns me ABC and DEF which is perfect.
Now I want to check for all booking that have a overlapping period based on numberOfdays
So for example get me all the bookings that are within the period mentioned by startDate and endDate based on numberOfDays
INPUT: For example if startDate is 11-08-2021 and endDate is 12-08-2021 then
Expected Output: the bookings returned are JKL, ABC and DEF since JKL has booking for 2 days which will overlap with startDate 11-08-2021
Any pointer will be appreciated
CodePudding user response:
Since you're not saving end date you need to calculate it as start_date interval numberOfDays days
to get the exclusive end date. The date overlap query would look like:
SELECT *
FROM t
WHERE @d2 > start_date AND (start_date interval numberOfDays day) > @d1
-- In order to check [2021-08-11, 2021-08-12]
-- You will set @d1 and @d2 to 2021-08-11 and 2021-08-13
For completeness, here is the inclusive end date version:
SELECT *
FROM t
WHERE @d2 >= start_date AND (start_date interval numberOfDays - 1 day) >= @d1
-- In order to check [2021-08-11, 2021-08-12]
-- You will set @d1 and @d2 to 2021-08-11 and 2021-08-12