Algorithms – How to Figure Out Appointment Times

algorithms

I have a weird situation where a client would like a script that automatically sets up thousands of appointments over several days. The tricky part is the appointments are for a variety of US time zones, and I need to take the consumer's local time zone into account when generating appointment dates and times for each record.

Appointment Rules:

  • Appointments should be set from 8AM to 8PM Eastern Standard Time, with breaks from 12P-2P and 4P-6P. This leaves a total of 8 hours per day available for setting appointments.

  • Appointments should be scheduled 5 minutes apart. 8 hours of 5-minute intervals means 96 appointments per day.

  • There will be 5 users at a time handling appointments. 96 appointments per day multiplied by 5 users equals 480, so the maximum number of appointments that can be set per day is 480.

  • Now the tricky requirement: Appointments are restricted to 8am to 8pm in the consumer's local time zone. This means that the earliest time allowed for each appointment is different depending on the consumer's time zone:

    • Eastern: 8A
    • Central: 9A
    • Mountain: 10A
    • Pacific: 11A
    • Alaska: 12P
    • Hawaii or Undefined: 2P
    • Arizona: 10A or 11A based on current Daylight Savings Time

Assuming a data set can be several thousand records, and each record will contain a timezone value, is there an algorithm I could use to determine a Date and Time for every record that matches the rules above?

Best Answer

I figure I would throw my comments together into a full answer:

Data Model

User - The abstraction of your user:

  • UserId

  • TimeZone

Appointment - A scheduled appointment:

  • AppointmentId

  • UserId

  • DateRange (5 minutes starting at a UTC timestamp)

Day - Borrowing heavily from your answer Rachel, this object will be a derived list of all appointments for a single Day...

BUT... The idea of a single Day is a floating concept. Depending on what TimeZone the user is coming from then one persons Day may overlap another persons Day, therefore Day is relative to a users Timezone, and thus the DateRanges in the Appointments for that Day could be completely different depending on what Timezone we are talking about.

To Fetch a Day's Appointments: Given a Timezone, determine the list of static DateRanges, 8am-8pm with breaks. Next fetch all the Appointments by DateRange that fall within this.

Concurrency

The active schedule is time sensitive and represent a limited quantity resource that users are competing for (you have to look at it in these terms). Locking the Appointment table during validation and scheduling for a single user is necessary to preven double booking.

Algorithm

Once you have fetched a Day for a given user and their respective Timezone:

  • Loop through the static Day DateRange's and make sure that the requested appointment time falls within one of these ranges.

  • Next loop through the already scheduled appointments and make sure that the requested appointment time does not overlap with any existing scheduled appointments.

  • If these validations pass then create a new Appointment with its appropriate DateRange and persist.

  • Lather rinse and repeat...

Related Topic