Calculate Overlapping Nights between reservations and a report date range

[fedeyobe]

Hello!, I’m trying to calculate the number of overlapping nights between reservation periods and a reporting period in my AppSheet app, but I’m running into issues because the relevant date fields are in different tables.

I have two tables in my app:

1. Reservations (registro_reservas):

   • Contains [f_checkin] and [f_checkout] dates.

2. Reports (reporte_economico):

   • Contains [desde_reporte] (start date) and [hasta_reporte] (end date) for each report.
   • Also includes a virtual column (e.g., [AllReservas]) that lists relevant reservations.

I want to calculate the number of overlapping nights between a reservation and a report’s period. For example:

• Scenario 1:

  • Reservation: Jan 25, 2025 to Feb 2, 2025 (8 nights)
  • Report: Jan 1, 2025 to Jan 31, 2025
  • Expected overlap: 6 nights (Jan 25–31)

• Scenario 2:

  • Reservation: Dec 28, 2024 to Jan 5, 2025 (8 nights)
  • Report: Jan 1, 2025 to Jan 31, 2025
  • Expected overlap: 4 nights (Jan 1–5)

I tried this formula to calculate overlapping nights:

MAX(
0,
MIN([f_checkout], [hasta_reporte]) - MAX([f_checkin], [desde_reporte])
)

It works if all fields are in one table, but my reservation dates and report dates are in different tables. I need a solution that lets each report (with its own dates) calculate overlapping nights for its reservations.

How can I compute the overlapping nights for each reservation relative to a report’s period in AppSheet?

I'm doing this because i need to calculate the percentage of occupation of a few rental properties.!

Any advice or recommended approaches (e.g., using a join table or another method) would be greatly appreciated!