0

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

Improve this question
2
  • You also need to specify the expected result. Commented Nov 8, 2021 at 10:40
  • I have mentioned it.. I will make it more clear Commented Nov 8, 2021 at 10:41

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks.. Is it ok @d2 >= DATE(start_date) and then not changing the end date at all?
You want to use [2021-08-11, 2021-08-12] as-is?
Yes, the startDate and endDate which would be used for comparison will be those two dates. So when its exact match its only booking with 11 and 12 August (first query) and if its to be checked against all the bookings that are in the interval then your query would come into picture
See revised answer. Basically both > must be changed to >= and end date must be start date + (n - 1) days.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.