Solved Counting days from multiple date periods

[AlexN]

Hi everyone,



Here I am again with a really tough brain teaser.

We have some events going on, in which Customers (tblCustomers), rent Seats (tblSeats) to attend the event, paying a daily Rate (tblRates) who’s Min and Max values are being predefined by the user, according to Seat Category (tblSeatCategories) and season of the year (tblSeasons). So daily Rates for the same Seat, vary through the year according to Seasons. Seasons are also being predefined by the user and, we want users to be able to set their own seasons every time necessary, as well as keep track of older ones.

Then we have Rentals (tblRentals) that hold information like, when a seat was rented (RentalStart-RentalEnd), by whom (CustomerID), which Seat was rented (SeatID) and subsequently its category, and at what Rate (RentalRate). This Rate has to be a value between Min and Max values predefined in tblRates for the Seat(-> SeatCategory) rented and for the Season, and it’s an input by the user during the (let’s say) “booking” process.

So, user has to have the Rates information for Seat Category and Season on display, during the “booking” process, to decide the Rate.(presumably a query-based subform or a recordsource setting piece of vba code.). We want a subform that would show Min and Max rates for the given Seat Category, and for the given Season that’s calculated after RentalStart and RentalEnd inputs. We also want that subform to show the numbers of days the Rental lasts (multiple durations if multiple seasons).

When Rental’s duration is within the date limits of a single season that’s easy. Even when rental exceeds the date limits of a single season and continuous for the next one, that info is easily calculated (done). But…



Problem is…

There will be times that a rental will (eg) start middle of season 1, last the whole season 2, and end middle season 3 (or even some seasons later). I can count the days within first season, can count the days within last season, but can’t (trying for over a week) find a way to count the days in between (Season's duration doesn't have to be standard).

Any ideas?

Posting a sample piece of a database for anyone who decides to bother.



Thanks in advance

 

[arnelgp]

you should fix table Rental, it should add the RateID from rate table since this table holds the "junction" between Season and SeasonCategory.

if you do that, you can directly link Rental to Rate table and rate table join to season and seasonCategory.
then you know the dates in between this season.

 

[ebs17]

You should be able to find a price for a seat for a given day, along with the associated terms and conditions. Your data model must allow this.

Then you can break down a rental period into individual days using a calendar table. This large result table can then be summarized again using groupings based on the conditions and everything can be evaluated as desired.

Larger amounts of data will be created along the way. You have to be able to deal with this by using good query design and using indexes.

 

[AlexN]

Well thank you both @arnelgp and @ebs17 for answering but your suggestions don't seem to do the job.

Maybe I should be more clear :
When user registers a "booking", first thing he does is passing down the dates (RentalStart, RentalEnd), then the Customer (CustomerID) and then the seat (SeatID). At this moment he have the SeatCategory too. We also have the duration of the rental.

User now wants to have info on display about room rates for this seat category (how many days in which season) so he sees the min and max rates and he can decide what rate he gives to the customer (a single price for the whole rental). User wants to know how many days of season 1, how many days of season 2 (and if it happens, how many days of season 3 - see RentalID=3 in sample) the rental will last, in order to decide a single price (RoomRate in tblRentals), that would approximately be an average of all rates, to give to the customer. He doesn't want to know the rate of a given day. After all, that's not set, only min and max values are set.

Thanks

 

[ebs17]

Counting days is clear:

SELECT
   tblRentals.CustomerID,
   tblRentals.SeatID,
   tblSeasons.SeasonName,
   COUNT(*) AS XDays
FROM
   tblCalendar,
   (tblSeatCategories
      INNER JOIN
         (tblSeats
            INNER JOIN tblRentals
            ON tblSeats.SeatID = tblRentals.SeatID
         )
      ON tblSeatCategories.SeatCategoryID = tblSeats.SeatCategoryID
   )
   INNER JOIN
      (tblSeasons
         INNER JOIN tblRates
         ON tblSeasons.SeasonID = tblRates.SeasonID
      )
   ON tblSeatCategories.SeatCategoryID = tblRates.SeatCategoryID
WHERE
   tblCalendar.CalendarDay BETWEEN tblRentals.RentalStart
      AND
   tblRentals.RentalEnd
      AND
   tblCalendar.CalendarDay BETWEEN tblSeasons.SeasonStart
      AND
   tblSeasons.SeasonEnd
GROUP BY
   tblRentals.CustomerID,
   tblRentals.SeatID,
   tblSeasons.SeasonName

 

[AlexN]

Thanks but, in order to test it, I need a tblCalendar filled up with calendar dates. How on earth am I gonna do that.

 

[ebs17]

In the very, very simple case and for those who cannot program at all:
Open Excel with an empty spreadsheet, cell A1 is given the title CalendarDate, cell b1 is entered e.g. 2022-01-01 (date format according to country setting), then drag this date down to e.g. 2023-12-31. Whole calendar years are always useful. Save the workbook and import the table into your Access application. The CalendarDate field should be set as the primary key.
Of course, you can also create the table using DDL, DAO or ADOX and then fill it using a loop or query.

 

[AlexN]

In the very, very simple case and for those who cannot program at all:
Open Excel with an empty spreadsheet, cell A1 is given the title CalendarDate, cell b1 is entered e.g. 2022-01-01 (date format according to country setting), then drag this date down to e.g. 2023-12-31. Save the workbook and import the table into your Access application. The CalendarDate field should be set as the primary key.
Of course, you can also create the table using DDL, DAO or ADOX and then fill it using a loop or query.

Ok I'm one of those who cannot program at all. Never said I wasn't, on the contrary, I keep saying I'm trying to learn something here. But opening an excel worksheet filling it with dates and then copying it to an access table isn't quite an automated way of doing things. And what happens if dates copied reach the last one. Should we all close our eyes then ?
If this tblCalendar is so necessary, isn't there a way for autofilling it with dates giving a start date and going on for the next (let's say) 20 years?

Thanks

 

[Gasman]

Thanks but, in order to test it, I need a tblCalendar filled up with calendar dates. How on earth am I gonna do that.

Search here. arnelgp posted code to do exactly that.

I think it went along the lines of
Create 3 tables, one for Days of the month, one for Months, one for required years.
Create your calendar table, mine is called tblDates

Then run this query

INSERT INTO tblDates ( DayDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between [Forms]![frmGenerateDates]![txtStartdate] And [Forms]![frmGenerateDates]![txtEnddate]) AND ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);

 

[Gasman]

Ok I'm one of those who cannot program at all. Never said I wasn't, on the contrary, I keep saying I'm trying to learn something here. But opening an excel worksheet filling it with dates and then copying it to an access table isn't quite an automated way of doing things. And what happens if dates copied reach the last one. Should we all close our eyes then ?
If this tblCalendar is so necessary, isn't there a way for autofilling it with dates giving a start date and going on for the next (let's say) 20 years?

Thanks

You would link to that excel sheet as you calendar table?

Or you could try @arnelgp 's method, however that also requires some programming?

 

[AlexN]

You would link to that excel sheet as you calendar table?

Or you could try @arnelgp 's method, however that also requires some programming?

I don't think linked excel sheet is the best way. That means that every time dates reach the last one, user would have to open excel, refill the sheet with new dates and so on, provided user has excel installed and knows what to do.
I'll try @arnelgp's method as soon as I find it, and I'm not afraid of a little additional programming, after all I'm hear to learn.

Thank you so much

 

[ebs17]

Your desire to learn honors you.

I'm using a number table T999 as a basis, which contains the numbers from 0 to 999. This helps in many cases. You can use DateAdd to create the days of a calendar year. Compare the procedures CreateCalendarTable and FillCalendarTable in the attachment.

Important note: A well-planned calendar table contains additional columns with calculated and stored formats from the date: year, month, calendar week, quarter, fiscal year and whatever else is specifically needed. These columns are also indexed.

A test with an experimental arrangement is included in the attachment, which shows that looking up date formats that are also required for actions such as grouping is faster than recalculating such formats over and over again.

 

[Gasman]

I don't think linked excel sheet is the best way. That means that every time dates reach the last one, user would have to open excel, refill the sheet with new dates and so on, provided user has excel installed and knows what to do.
I'll try @arnelgp's method as soon as I find it, and I'm not afraid of a little additional programming, after all I'm hear to learn.

Thank you so much

Well I have given you the basics that I am sure his method was based on? That worked for me for generating required dates.
Whichever way, you are going to need to update in someway.
If in Access, you could check on opening how many days are left, and if less than n months, generate a nother few years worth of dates?

 

[AlexN]

Counting days is clear:

SELECT
   tblRentals.CustomerID,
   tblRentals.SeatID,
   tblSeasons.SeasonName,
   COUNT(*) AS XDays
FROM
   tblCalendar,
   (tblSeatCategories
      INNER JOIN
         (tblSeats
            INNER JOIN tblRentals
            ON tblSeats.SeatID = tblRentals.SeatID
         )
      ON tblSeatCategories.SeatCategoryID = tblSeats.SeatCategoryID
   )
   INNER JOIN
      (tblSeasons
         INNER JOIN tblRates
         ON tblSeasons.SeasonID = tblRates.SeasonID
      )
   ON tblSeatCategories.SeatCategoryID = tblRates.SeatCategoryID
WHERE
   tblCalendar.CalendarDay BETWEEN tblRentals.RentalStart
      AND
   tblRentals.RentalEnd
      AND
   tblCalendar.CalendarDay BETWEEN tblSeasons.SeasonStart
      AND
   tblSeasons.SeasonEnd
GROUP BY
   tblRentals.CustomerID,
   tblRentals.SeatID,
   tblSeasons.SeasonName

Filled a tblCalendar with dates from 01/01/2021 to 31/12/2040. Then ran this query. Got this:

This is not what I want, and it's wrong.
In the attached sample, RentalID =3 lasts through 3 different seasons, as they are defined in tblSeasons.
It's SeasonID(5) (from 15/12/22 to 31/12/22, 16 days), SeasonID(6) (from 01/01/2023 to 28/02/2023, 59 days) and SeasonID(7) (from 01/03/2023 to 31/03/2023, 31 days). I want to be able to show that information to the user as soon as he inputs RentalStart, RentalEnd and SeatID.

Thank you

 

[AlexN]

Your desire to learn honors you.

I'm using a number table T999 as a basis, which contains the numbers from 0 to 999. This helps in many cases. You can use DateAdd to create the days of a calendar year. Compare the procedures CreateCalendarTable and FillCalendarTable in the attachment.

Important note: A well-planned calendar table contains additional columns with calculated and stored formats from the date: year, month, calendar week, quarter, fiscal year and whatever else is specifically needed. These columns are also indexed.

A test with an experimental arrangement is included in the attachment, which shows that looking up date formats that are also required for actions such as grouping is faster than recalculating such formats over and over again.

Found this code to automatically fill with the desired number of dates the existing tblCalendar:

Public Sub addSeqDates(dateFldName As String, tblName As String, recordsToAdd As Integer)
  Dim maxDate As Variant
  Dim strSql As String
  Dim i As Integer
  maxDate = DMax(dateFldName, tblName)
  If Not IsNull(maxDate) Then
   For i = 1 To recordsToAdd
     strSql = "Insert into " & tblName & "(" & dateFldName & ") values (#" & Format(maxDate + i, "mm/DD/YYYY") & "#)"
     Debug.Print strSql
     CurrentDb.Execute strSql
   Next i
  End If
End Sub

This makes it a lot more easy, but...I'm still not sure that tblCalendar is really needed. Haven't seen its goodies so far

 

[ebs17]

and it's wrong

I used your data model, your tables with the data as is. You should therefore first look for an error in this specification.

You can remove the grouping in the query and display additional fields for the tables to compare whether everything you need is included.

 

[arnelgp]

i made a form, Form1.
open it and see if this is near to what you need.

 

[AlexN]

i made a form, Form1.
open it and see if this is near to what you need.

thanks, yeah that's the way it should somehow be, but data is wrong.
In RendalID =2 subform has to have two records (cause it's going through 2 seasons) and in RentalID=3 subform should have 3 records (cause it runs through 3 seasons).

Only RentalID=1 is right.

In the attached sample, RentalID =3 lasts through 3 different seasons, as they are defined in tblSeasons.
It's SeasonID(5) (from 15/12/22 to 31/12/22, 16 days), SeasonID(6) (from 01/01/2023 to 28/02/2023, 59 days) and SeasonID(7) (from 01/03/2023 to 31/03/2023, 31 days). I want to be able to show that information to the user as soon as he inputs RentalStart, RentalEnd and SeatID.

Thanks

 

[AlexN]

The logic for this is the following:
If Rental duration is all in a single season things are easy. User picks a rate between min and max values and closes the deal.
If Rental duration exceeds one season, user has to easily know, how many days rental lasts for the first season and how many days for the other season. He then picks a rate of a value closer to rates of the season with more days.
So, if rental lasts 3 days of season 1 and 15 days of season 2, he has to pick a rate closer to season's 2 rates.
Edit:

I've managed to accomplish that by counting days between RentalStart and SeasonEnd for the 1st season and days between SeasonStart and RentalEnd for the 2nd season.
But if Rental lasts three or more seasons (as in RentalID=3) I can't manage to bring up days for the season in the middle.

Hope this clarifies it a bit

 

[ebs17]

I used your data model

That means: It doesn't match your requirement. Fix that.