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!
rick_dangerAsked:
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.

Dale FyeCommented:
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
hnasrCommented:
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
rick_dangerAuthor Commented:
where are you expecting to get "BookingDates.BookingDate" from please
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hnasrCommented:
@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
Dale FyeCommented:
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
rick_dangerAuthor 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
Dale FyeCommented:
no attachment provided
0
rick_dangerAuthor Commented:
you're not wrong
Database---Copy.zip
0
rick_dangerAuthor Commented:
Just go to the queries and you'll see them there
0
hnasrCommented:
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
rick_dangerAuthor 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
hnasrCommented:
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
rick_dangerAuthor 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
hnasrCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
rick_dangerAuthor Commented:
Very impressed by your patience and quality of the solution.

Thanks!
0
hnasrCommented:
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.