15,995,305 members
See more:
Good Afternoon,

I am working on solving a scheduling problem for a company. Here are a few facts:

The company begins projects up to several times each week. This year they have 75 projects that have either already started or will start before the end of the year. Regardless of when the project is completed, all are considered 2018 projects if they begin on any day in 2018.

Each project is composed of a predefined set of tasks.

Each task can require a different number of mechanics with different specialized skill sets. However, any mechanic with the needed skill set can work on any task the needs their skillset.

Each task also has to be performed in a specific workshop and requires specific tools.

Some tasks require the same tools and some do not. Tools are not viewed as pre-packaged kits, but as a individual needs for a specific task that is being performed.

In order to maximize scheduling efficiency for mechanics, workshops, tools, each project can follow one of 8 different project designs.

The projects can last from 2 hours up to several hundred work days.

Tasks can last from 15 minutes to 8 hours.

Work days are 8 hours long with a 1 hour lunch break.

Some work days are 12 or 14 hours long. However each of these days occurs at a specific time depending on which project design is being used.

I am able to create a composite work schedule for projects. The company only works Monday thru Friday and takes off recognized holidays. Since the date that a project begins is scheduled in advance we are able to easily develop a daily schedule of tasks to be completed using simple SQL queries.

The difficult part, and the reason for my post, is determining the minimum number of mechanics that the company needs to have on duty each day to cover the tasks that are scheduled. As well as determining the hours that we need to schedule those mechanics to work each day.

What I have tried:

I have used basic SQL to determine which hour long time periods during the day require mechanics to be on duty and how many need to be available during each specified time period. However, this does not answer the ultimate question, "What is the minimum number of mechanics that need to be on duty each day?"

Has anyone ran into a similar situation? Does anyone have any recommendations on how to calculate this number?
Posted
Updated 19-Aug-18 13:49pm
CHill60 20-Aug-18 7:57am
"Has anyone ran into a similar situation? " - Only as an exercise for my coursework.
"Does anyone have any recommendations on how to calculate this number? " - you have already worked out "how many need to be available during each specified time period", so the minimum number of mechanics that need to be on duty each day is surely the maximum of how many mechanics are required for any time slot in that day?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Dave Kreskowiak 45 OriginalGriff 25 RickZeeland 10 Richard Deeming 10
 OriginalGriff 431 Pete O'Hanlon 215 Dave Kreskowiak 115 CPallini 110 CHill60 42

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900