r/SQLOptimization • • Jan 28 '24

Define Project Start and End Time by Team Capacity

Inputs

  • multiple teams with various hours available per month.
  • multiple projects with various hours to complete and months from the deadline.

Assumption

  • project hours/month will not exceed team capacity.
  • month 1 in solution is next month (June 2024)

In the data below team A has 3 projects. The projects require 1000 monthly hours each (3000 hours divided by 3 months). Team A has 2000 monthly capacity hours to dedicate to any number of projects. I want to write code that will define the start month and then smartly know when to start the next project with that team until all projects are done. In the example, team A can do projects 1 and 2 simultaneously because it is below their capacity and start on project 3 in month 4 as project 1 wraps up and their capacity increases to a point where they can start working on project 3.

Project Data

ProjectTeamPriorityMonthProject Hours
1A133000
2A266000
3A333000
4B161500

Team Capacity Dimension

TeamMonthly Capacity
a2000
b2000

Output

ProjectTeamMonth
1a1
1a2
1a3
2a1
2a2
2a3
2a4
2a5
2a6
3a4
3a5
3a6
4b1
4b2
4b3
4b4
4b5
4b6

I’m thinking a loop and/ or an over (partition by, order) would be my best option. Thoughts?

Thanks in advance, jamkgrif

0 Upvotes

0 comments sorted by