Logic For Task: Assign staff to dates, avoiding vacations

• JJ B

  SSCertifiable

  Points: 5100

  More actions

  March 24, 2015 at 4:39 pm

  #150873

  I'm wondering if someone can help me with just figuring out the general logic/algorithm for what I need. Or point to an article on this topic. If this isn't the correct forum, I apologize and would appreciate a hint on where to post this question.

  MY CURRENT STORED PROCEDURE DOES THIS: Takes a list of staff and assigns those staff to a list of business days in a month. The assignments are done in a circle/rotation. For example, we start with Staff Person A and assign her to Date 1 (say Monday). Then the next Staff Person in the list, B, gets assigned to Date 2 (Tuesday), etc until the business days in the month are filled up. After the end of the list of staff is reached, the assignments start again at the beginning of the list of staff.

  After this initial auto-assignment, a user can then bring up a separate screen that allows her to reconcile the initial assignments with a list of approved vacation and sick leave days. So, the initial assignment list is massaged a bit manually until there are no conflicts between assigned days and days the staff have off work.

  THE NEW REQUEST IS FOR THIS: I have been asked to fully automate the second part. They want the stored procedure to look at the assignments and then auto-move staff around until there are no conflicts with the vacation/sick leaves.

  I have tried looking this problem up multiple ways on the internet in general and on SQLServerCentral, but I can't seem to come up with the correct search terms. For example, a search that includes 'scheduling' tends to return articles on Agent. I assume that this is a common type of problem and that if I knew what type of problem it is, I could find articles that explain the general algorithm for this type of task/can give pseudo code that I could translate to use in a stored procedure.

  Does this make sense? Any thoughts?

  Thanks!