Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Complicated logic around dates

Posted on 2014-07-12
Medium Priority
201 Views
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!
0
Question by:rick_danger
• 7
• 6
• 3

LVL 49

Expert Comment

ID: 40192070
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

LVL 31

Expert Comment

ID: 40192385
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 Comment

ID: 40192867
where are you expecting to get "BookingDates.BookingDate" from please
0

LVL 31

Expert Comment

ID: 40192878
@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

LVL 49

Expert Comment

ID: 40192900
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 Comment

ID: 40193202
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

LVL 49

Expert Comment

ID: 40193222
no attachment provided
0

Author Comment

ID: 40193337
you're not wrong
Database---Copy.zip
0

Author Comment

ID: 40193339
Just go to the queries and you'll see them there
0

LVL 31

Expert Comment

ID: 40193403
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 Comment

ID: 40193413
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

LVL 31

Expert Comment

ID: 40193444
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 Comment

ID: 40194182
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

LVL 31

Accepted Solution

hnasr earned 2000 total points
ID: 40194679
Try this. Added the form" Form1, with date parameters.
Go command, updates the table days, and runs Query1
Database---Copy-3.mdb
0

Author Closing Comment

ID: 40194789
Very impressed by your patience and quality of the solution.

Thanks!
0

LVL 31

Expert Comment

ID: 40194899
Welcome!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients wereâ€¦
If youâ€™re using QODBC to update QuickBooks data from MicrosoftÂ® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, youâ€¦
###### Suggested Courses
Course of the Month14 days, 20 hours left to enroll