# Complicated logic around dates

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!
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I use a separate table (tbl_Numbers), which contains a single field (lngNumber), and 10 records (the values 0-9).  From that table, I can generate a list of numbers with a query (qry_Numbers) as long as I want, but generally only need to go from 0-99.  That query looks like:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

With that query, I can now create another query that gives me a range of dates:

SELECT DateAdd("d", lngNumber, [StartDate]) as BookingDate
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, [StartDate]) <= [EndDate]

You can then use that as a subquery with your booking table, something like:

SELECT BookingDates.BookingDate
, Count(iif(tbl_Bookings.booking_Start_Date <=BookingDates.BookingDate AND
tbl_Bookings.booking_End_Date >= BookingDates.BookingDate), 1, 0)
FROM (
SELECT DateAdd("d", lngNumber, [StartDate]) as BookingDate
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, [StartDate]) <= [EndDate]
) as BookingDates, tbl_Bookings
GROUP BY BookingDates.BookingDate
0
Commented:
Try fyed's comment.
Check for start date modification :    >startDate  instead of >=
If issue persists:
Upload a table to save me time.
Include few representative records and the required output.
Is the data for the current year?
0
Author Commented:
where are you expecting to get "BookingDates.BookingDate" from please
0
Commented:
@rick,

Upload what you have, so ideas can be applied using your data.
"Complicated logic around dates"
It should be a simple approach, what makes it to look complicated is the degree of understanding.

To test our ideas we need to recreate the problem and as you mentioned "I have a database which records bookings at a hotel. ", so we ask for uploading a demo database.
0
Commented:
BookingDates.BookingDate comes from the subquery.  That is the piece that generates a list of the days between your [StartDate] and [EndDate].

SELECT BookingDates.BookingDate
, Count(iif(tbl_Bookings.booking_Start_Date <=BookingDates.BookingDate AND
tbl_Bookings.booking_End_Date >= BookingDates.BookingDate), 1, 0)
FROM (
SELECT DateAdd("d", lngNumber, [StartDate]) as BookingDate
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, [StartDate]) <= [EndDate]
) as BookingDates
, tbl_Bookings
GROUP BY BookingDates.BookingDate
0
Author Commented:
I can't actually get that to work. It falls over with a problem regarding the iif statement. I have attached an abridged copy of the database.
0
Commented:
no attachment provided
0
Author Commented:
you're not wrong
Database---Copy.zip
0
Author Commented:
Just go to the queries and you'll see them there
0
Commented:
The table has many records. Can you define few records and list the expected output?
Example:
BookingID      StartDate      EndDate      customer
6      21-Jan-11      24-Jan-11      Mr Smith
8      20-Jan-11      24-Jan-11      Mr Smith
23      03-Feb-11      05-Feb-11      Mr Smith
24      01-Feb-11      05-Feb-11      Mr Smith
25      01-Feb-11      06-Feb-11      Mr Smith
26      28-Jan-11      06-Feb-11      Mr Smith
27      28-Jan-11      06-Feb-11      Mr Smith
28      04-Feb-11      07-Feb-11      Mr Smith
29      04-Feb-11      08-Feb-11      Mr Smith

How do you want the output?
Is it for minimum date, then for next days?
Just list expected output.
0
Author Commented:
I want to enter a start date and end date In a form, then the query will list the number of bookings for each day within that period in ascending date order.  More or less as In my original post but without the text in the brackets.
0
Commented:
Try this:
table Days, can be created from days entered in form.
Qwery1 displays day and number of bookings.
Check and comment.
Database---Copy-2.mdb
0
Author Commented:
Sorry, but don't know how I can use days entered into a form to use as criteria. If I am using:
forms!frm_enter_dates.start_date and forms!frm_enter_dates.end_date,

where would I enter these into the queries please?
0
Commented:
Try this. Added the form" Form1, with date parameters.
Go command, updates the table days, and runs Query1
Database---Copy-3.mdb
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Very impressed by your patience and quality of the solution.

Thanks!
0
Commented:
Welcome!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.