I have a table hotel_booking
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
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