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
16
Medium Priority
?
201 Views
Last Modified: 2014-07-14
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
Comment
Question by:rick_danger
  • 7
  • 6
  • 3
16 Comments
 
LVL 49

Expert Comment

by:Dale Fye
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

by:hnasr
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

by:rick_danger
ID: 40192867
where are you expecting to get "BookingDates.BookingDate" from please
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Expert Comment

by:hnasr
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

by:Dale Fye
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

by:rick_danger
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

by:Dale Fye
ID: 40193222
no attachment provided
0
 

Author Comment

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

Author Comment

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

Expert Comment

by:hnasr
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

by:rick_danger
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

by:hnasr
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

by:rick_danger
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

by:
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

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

Thanks!
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40194899
Welcome!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question