SQL all the days between two dates

Question

I am currently in a bit of a pickle trying to sort out some queries from one of our databases. The table that is giving me issues looks pretty much as follows:

id   | bookingStartDate | bookingEndDate | bookingLength |  customerID | roomNumber
-----------------------------------------------------------------------------------
310  | 2016-11-10       | 2016-11-17     |     7         |    004943   |    583
443  | 2017-01-15       | 2016-11-29     |     14        |    008745   |    345

What I am trying to achieve is to provide a good idea of the availability of specific rooms over a specific window of time. The way the data is currently stored, I do have access to both the start and the end of the booking as well as the amount of nights the booking will last for.

What I am trying to do is create a query to check the availability of a specific room given a range of dates, such as from then 10th of November to the 20th of November 2016.

In order to solve it I have tried left joining a calendar table with this one and filtering on the room number. The join however is relying on the bookingStartDate or the end one and I am not sure on how to proceed to instead get the data to show the days in between the two when the room is also occupied.

Any help or suggestion would be awfully appriciated!

Your query thus far and a sample of expected output would help. — dazedandconfused, Oct 12 '16 at 13:08
If you really do have a bookingStartDate that is after the bookingEndDate (as shown), your bad data is going to cause you problems even when testing a valid query. — coladict, Oct 12 '16 at 13:32

score 0 · Accepted Answer · answered Oct 12 '16 at 13:23

0

Using a calendar table:

set @PeriodStart = '2016-01-01';
set @PeriodEnd = '2017-12-31';
set @Room = 583;

select Ca.Date,
       Bo.RoomNumber,
       Bo.CustomerID

from Calendar Ca
left join Booking Bo
    on Ca.Date between Bo.StartDate and Bo.EndDate

where Ca.Date between @PeriodStart and @PeriodEnd
and RoomNumber = @Room ;

This will show all dates in the period between @PeriodStart and @PeriodEnd, and any bookings for the period for that room

answered Oct 12 '16 at 13:23

JohnHC

10,935
1
24
40

I think you must defined better Calendar table. By your answer appears this is a system table. Syntax used is Sql Server but question is on MySql – Joe Taras Oct 12 '16 at 13:26
@JoeTaras The OP has already said he has produced a calendar table. There's a ton of guidance out there, [here](http://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates), for example – JohnHC Oct 12 '16 at 13:29

SQL all the days between two dates

1 Answers1