I am working with a database schema shown below and I am struggling with writing a query on that schema.
A hotel has a contract with a period of validity. Also it has many seasons, and each season has many periods. The task is to return all dates from the contract validity period that are not available in the seasonal periods.
This is my schema:
Ideally, I would like to get all possible intervals that do not cover the periods of the hotel. But if it is not possible then at least a list of dates.
Example (all dates are in the DD.MM.YYYY format):
- Contract period: 1.01.2018 – 31.12.2018
- Hotel periods: 1.01.2018 – 30.06.2018; 1.08.2018 – 31.12.2018
In this case I lose one month 1.07.2018 – 31.07.2018.
And I want to get this period, or several periods if there happen to be several. So, something like this:
BeginDate EndDate --------- ---------- 1.07.2018 31.07.2018
If it's not possible to get a list of periods, then I want to get all missing dates, and group them in periods on the server side.
Best Answer
If a hotel can never have overlapping seasonal periods, then it is possible to obtain the result as a list of intervals by direct matching of intervals using the method below.
First you need to invert the seasonal period list, which means getting the list of gaps between the items of the list complementing it with two more intervals representing the period before the first item and the period after the last item. In other words, a list like this:
would be transformed to a list like this:
The
date_fromi
anddate_toi
in the second list refer to the corresponding values from the first list. The one-day adjustments are there to account for the fact that both the input intervals and the output intervals are inclusive.This is how you could generate the list of gaps in SQL based on your schema:
Note that adjacent seasonal periods will generate "gaps" for which
date_from > date_to
. Such ranges would be invalid, of course, and you would need to filter them out before further processing.The further processing will involve finding the gaps intersecting with the hotel contract period. Those that do intersect with it will actually be the intervals that you want to return, except the you may need to adjust the beginning of the first matching gap as well as the end of the last one, because those might go outside the contract period bounds.
This is the matching condition, which takes into account that both the contract period and the gaps are inclusive intervals:
That is, a gap is considered as intersecting the contract period when its end either comes after or exactly matches the contract beginning while at the same time the gap's beginning either comes before or exactly matches the contract's end.
As I have already said, the first and the last matching gap may go partly outside the contract period, i.e. like this:
For the output you will want to modify them like this:
This means you will need to calculate both the beginning and the end for each gap: take the latest between
gap.date_from
andcontract.date_from
as the beginning and the earliest betweengap.date_to
andcontract.date_to
as the end:Putting everything together, this is what you get:
For the
list_of_gaps
you can directly use the first query as a derived table:Or you can store that query's results in a temporary table first:
and use the temporary table as the
list_of_gaps
instead:The latter option – doing the job in two distinct steps – might be faster, depending on whether MySQL will find the single-query solution too complex to come up with an efficient plan, and also depending on the amount of data in your tables. Test both options for yourself to choose the one that works best for you.