Solved: Resources allocation over a date range - Not overl...

karimmc · 09-22-2021 05:37 PM

Hi!
I’ve been stuck for a while now with a problem that is more logical than technical but maybe someone in here has solved this before in any other context.

I created a TRIPS table, where users can schedule delivery trips. When scheduling a trip they select a START DATE, END DATE, a VEHICLE and a DRIVER.
What I have been trying to solve is that, once selected the START DATE and the END DATE, how could I validate that both the VEHICLE and the DRIVER appearing in the enum list of each field are just the ones that are available during the period of time selected.

At first I tried using a validation for the START TIME (should be greater than the END TIME of the last trip) and a validation for the end time (should be earlier than the START DATE of the next trip. However, this does not solve all the use cases because the user could be trying to schedule a trip in the far future or in between trips.

Any idea on how to solve this kind of problems?

Thanks in advance

karimmc

Thanks @bonameblisto and @Marc_Dillon.

Both solutions helped. On Andre’s solution I was only checking on current trips, but I also wanted to check for future trips. On Marc’s solution I was setting a validation for the trip but I wanted to take it a step forward showing in the drop down list only the vehicles available during that date rang.

So combining both of the solutions the expression would be something like this, I’ll type ot here in case anyone else has this same problem in the future.

SELECT(
	VEHICLES[vehicle_id],
        TRUE)
- SELECT(
	TRIPS[vehicle],
  OR(
       AND(
         ([start_date] >= [_THISROW].[start_date]),
         ([start_date] <= [_THISROW].[start_date])
       ),
       AND(
         ([end_date] >= [_THISROW].[start_date]),
         ([end_date] <= [_THISROW].[end_date])
       )
       AND(
         ([start_date] < [_THISROW].[start_date]),
         ([end_date] > [_THISROW].[end_date])
        )
    )
 )

Thanks!

View solution in original post

bonameblisto

SELECT(TRIPS[Vehicle],
AND(
[SRART DATE]<=[_THISROW][END DATE],
[END DATE]>=[_THISROW][START DATE]
)
)

is list of “busy” vehicles for selected period

“All vehicles” - “Busy vehicles” = “Free vehicles”

karimmc

Thanks @bonameblisto and @Marc_Dillon.

Both solutions helped. On Andre’s solution I was only checking on current trips, but I also wanted to check for future trips. On Marc’s solution I was setting a validation for the trip but I wanted to take it a step forward showing in the drop down list only the vehicles available during that date rang.

So combining both of the solutions the expression would be something like this, I’ll type ot here in case anyone else has this same problem in the future.

SELECT(
	VEHICLES[vehicle_id],
        TRUE)
- SELECT(
	TRIPS[vehicle],
  OR(
       AND(
         ([start_date] >= [_THISROW].[start_date]),
         ([start_date] <= [_THISROW].[start_date])
       ),
       AND(
         ([end_date] >= [_THISROW].[start_date]),
         ([end_date] <= [_THISROW].[end_date])
       )
       AND(
         ([start_date] < [_THISROW].[start_date]),
         ([end_date] > [_THISROW].[end_date])
        )
    )
 )

Thanks!

Marc_Dillon

Check out the section titled “Validate Non-Overlapping Date Range” here:

Resources allocation over a date range - Not overlapping - Solved