Data range and "snapshots"

[Greg Hains]

Hi.

 

I have a query that somebody may be able to help me with. When I see a solution I will kick myself for it being so simple, but at the moment I have gone in too many circles.

 

The concept is this:

There is a start date and a finish date - for the sake of ease, let's assume its a hotel (it's not by the way! lol.

The list of clients is shown with a book-in date, and once they've left, a checkout date.  Whilst they are still there, there is no checkout date.

 

I am looking to get a snapshot in two different scenarios:

 

The first scenario is to provide a single date (lets call it date#1) in the past and see what the occupancy status was at that time - so anybody that was in the facility at the time - including the check-in and check-out dates. This is a snapshot of what-was-what on that date.

 

The second scenario is to provide two dates (a range - date#1 and date#2) in the search process, list all occupancies including and between these two dates for check-in and checkout, which would, of course, provide more results.

 

My question is about the logic of applying the range Find in the checkin and checkout dates.

eg.  checkin_date...date#1...checkout_date (which I know is not the correct syntax).

 

Whats the best method of executing this find please?

 

Cheers,

Greg

 

[Lee Smith]

Check and see if this file here helps. Nightwing’s Booking System

[comment]

To make this simpler (at least for starters), let as consider a Bookings table with the following 4 fields:

• StartDate
• EndDate
• gRangeStart
• gRangeEnd

The following script:
 

Enter Find Mode[]
Set Field [ Bookings::StartDate; "≤" & Bookings::gRangeEnd ]
Set Field [ Bookings::EndDate; "≥" & Bookings::gRangeStart ]
Perform Find []

will find all bookings that overlap the given range, either fully or partially. Open-ended bookings will not be included; for this, you'll need to add another request before performing the find.

[Greg Hains]

Hi Lee.  Thanks for this - I will have a look at the NightWing product.

 

Hi Comment - yes, this is basically what I had - thanks.  Given that it would be a new request, the open ended occupancies would assume that the client is still occupying a room so therefore if their date of occupation was after EITHER the gRangeStart or gRangeEnd then it would come back as positive in the search. e.g. Found-True.  Would you agree with that?  

 

Many thanks to you both for taking the time.

 

Cheers,

Greg

[comment]

the open ended occupancies would assume that the client is still occupying a room so therefore if their date of occupation was after EITHER the gRangeStart or gRangeEnd then it would come back as positive in the search. e.g. Found-True.  Would you agree with that? 

 

Not really. In order to overlap with the given range, the open-ended booking must satisfy two conditions: [1] start before the end of the range; AND [2] be open-ended.

 

To incorporate this into the above script, you could do:

Enter Find Mode[]
Set Field [ Bookings::StartDate; "≤" & Bookings::gRangeEnd ]
Set Field [ Bookings::EndDate; "≥" & Bookings::gRangeStart ]
Duplicate Record/Request
Set Field [ Bookings::EndDate; "=" ]
Perform Find []

[Greg Hains]

Hi Comment.

 

Thanks for the extra information. I will apply it shortly and see how I go. :-)

 

Cheers,

Greg