Complicated logic around dates
Posted on 2014-07-12
I have a database which records bookings at a hotel.
I need to count the number of live bookings within a date range (start date to end date). So if we had a date range of 1st June to 5th June we would like to see how many live bookings are there for each day in that date period. Obviously some people will have arrived before 1st June, and some people will leave after 5th June. Also, some people may stay for 2nd and 3rd June.
So for these guests we have the following bookings:
Mr A - 29th May to 3rd June
Mr B - 3rd June to 9th June
Mr C - 2nd June to 3rd June
Mr D - 28th May to 12th June.
In this case, we would get the following results:
1st June - 2 live bookings (A, D)
2nd June - 3 live bookings (A, C, D)
3rd June - 4 live bookings (all)
4th June - 2 live bookings (B, D)
5th June - 2 live bookings (B, D)
If we assume the table is called tbl_booking and I need a count of the ID field, I presume. The date fields are called booking_start_date and booking_end_date. Also, assume only one guest per room for now.
So, I need to know how to write the SQL for this please!