Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
Is it the Date and Time for every record matching the rules above, or do you want a DateTime that is actually a wrapper for a primitive long that represents a single universal point in time? (Eg. 10am PST is the same timestamp as 8am EST). The timestamp is also agnostic of daylight savings time, timezones that are not an hour difference, etc... –  maple_shaft Jul 10 '12 at 17:24
    
@maple_shaft I'm not actually sure what you're asking... The end result I'm looking for is an algorithm that assigns a UTC DateTime value to each record. The dates/times should follow the rules set in the first 3 bullet points (meaning they should be spaced 5 minutes apart, there can be 5 set for the same time, and they should all be set between the EST hours specified), and they should not be outside of 8am-8pm in the consumer's local time. –  Rachel Jul 10 '12 at 17:40
    
See my answer below... I finally organized my thoughts. –  maple_shaft Jul 10 '12 at 18:08
add comment

3 Answers

up vote 2 down vote accepted

I thought of an algorithm I think will work for this

I'll start by creating a structure to track appointments as they get set. It can be something as simple as this:

class Day
{
    public DateTime Date { get; set; }
    public List<DateTime> AssignedTimes { get; }

    // Assigns the next available time to a record, 
    // or returns false if no time is available
    public bool AssignNextAvailableTime(MyRecord record, int minHour)
    {
        // Get the next available time after minimum hour, 
        // based off of the list of already AssignedTimes, 
        // and whatever other logic is needed
        var nextAvailableTime = GetNextAvailableTime();

        if (nextAvailableTime == DateTime.MinValue) 
            return false;

        record.AppointmentDateTime = nextAvailableTime;
        AssignedTimes.Add(nextAvailableTime);

        return true;
    }
}

Loop through the most restrictive records (Hawaii or Undefined), and assign them to a date and time, adding a day if needed. Here's an example to demonstrate:

List<Day> days = new List<Day>();

foreach(var record in records.Where(r => r.TimeZone == null || r.TimeZone == "Hawaii"))
{
    foreach(var day in days)
        if (day.AssignNextAvailableTime(record, 14))
            break;

    if (record.AppointmentDateTime == null)
    {
        var day = new Day();
        day.AssignNextAvailableTime(record, 14);
        days.Add(day);
    }
}

Do the same thing with the rest of the time zones, going from the most restrictive to the least restrictive.

The end result will be that every record is assigned a date and time that is between 8a and 8p in the consumer's local time, and the dates/times will be as compact as possible, meaning the minimal number of days will be used to schedule all the appointments

share|improve this answer
    
Your on the right track, but just passing minHour isn't going to give you enough information for your algorithm. Your 8am-8pm(user TZ) is split with break times so your schedulable window would in reality be a List<DateRange>. Then your algorithm is simple, loop through each DateRange, if it doesn't overlap with existing appointment and falls within a DateRange then it can be scheduled. –  maple_shaft Jul 10 '12 at 17:33
    
Another word of advice, when dealing with multiple user concurrency and dealing with data that is time based and in limited quantity, it is important that you lock the table to make sure that somebody doesn't sneek an appointment out from under you after you have already validated that a block of time is clear. That is a mistake I have made before when writing scheduling code and it has caused me grief. –  maple_shaft Jul 10 '12 at 17:35
    
@maple_shaft Thanks :) The code here was just for demonstrating the idea, and isn't very optimized. I haven't even started writing the actual code yet, as I was just trying to figure out an algorithm that would work for calculating the dates first. –  Rachel Jul 10 '12 at 17:44
add comment

I would create an interface for the timezones using military (24 hour) time.

As in, for timezone x, EST= time; CST = time + 1; MST = time + 2;

and so on.

So, you could pass the timezone to your appointment function, and then time would be automatically calculated when you create or edit an appointment. In the appointment function, you could easily put in if/then logic for breaks, like:

    if((time>12:00 && time<14:00) || (time>16:00 && time<18:00)){
    //code for break time, no appointments made here, timezone lets you use the current timezone you're in
    }
share|improve this answer
    
Yes, I plan on using UTC time for all the dates, but was using EST time in my question because I was too lazy to do the math and convert the times I was given to UTC :) –  Rachel Jul 10 '12 at 17:47
add comment

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...

share|improve this answer
    
I'm reading through it now, however I think I may have been unclear about the requirements. The client wants to schedule thousands of appointments using the specifications in the first 3 bullet points. The client and all 5 users (employees) are in the EST timezone. Consumers, the person the client is scheduling the appointment with, can live in any time zone in the US (or undefined), and no appointment can be set earlier than 8am in the consumer's local time zone. So a Day should represent the client's workday and isn't timezone-specific. –  Rachel Jul 10 '12 at 18:26
    
In addition, the appointment dates and times are meant to all be set at once during a script, and are not meant to be added or edited by regular users. The idea is the client uploads a file of records every few weeks, and it generates appointment dates and times for every record. –  Rachel Jul 10 '12 at 18:26
    
@Rachel Okay, but Users and Consumers can interchangable in my answer still. In addition, the appointment dates and times are meant to all be set at once during a script, and are not meant to be added or edited by regular users. Then concurrency is actually NOT an issue, however now you have to determine who gets what spot and where. Is it random placement within a DateRange? If two users request the same appointment time is there an order of preference over who gets the desired spot? –  maple_shaft Jul 10 '12 at 19:13
1  
"... now you have to determine who gets what spot and where. Is it random placement within a DateRange?" That's what my question was about :) How to figure out how to schedule each record for an appointment date/time based on the rules outlined in the question. –  Rachel Jul 10 '12 at 19:22
    
There's actually two systems in the overall application. The piece I am currently working on is only responsible for automated scheduling of large batches of appointments. If consumers would like to change the date or time of their appointment, they are allowed to do that but it is tracked separately and handled in a different part of the application by a different group of users. The logic for that system is different from this one :) –  Rachel Jul 10 '12 at 19:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.