Hello experts,
I have 2 datasets -
The backlog of jobs, the hours for each job, and the week all material will be ready to work:
| RecordID | Job Number | Hours | Ready Week |
| 1 | 7716856 | 11 | 202407 |
| 2 | 7716856 | 12 | 202407 |
| 3 | 7713693 | 40 | 202409 |
| 4 | 7716442 | 19 | 202410 |
| 5 | 7714624 | 62 | 202410 |
| 6 | 7714624 | 174 | 202410 |
| 7 | 7715866 | 205 | 202410 |
| 8 | 7715867 | 153 | 202410 |
| 9 | 7711139 | 20 | 202410 |
| 10 | 7711139 | 30 | 202411 |
And a weekly crew capacity:
| Scheduling Week | Capacity (hours) |
| 202405 | 500 |
| 202406 | 500 |
| 202407 | 500 |
| 202408 | 500 |
| 202409 | 500 |
| 202410 | 500 |
| 202411 | 500 |
With consideration of the Ready Week, I am looking to assign these jobs and adjust capacity, and update the 2 original tables like this:
| RecordID | Job Number | Hours | Ready Week | Scheduled Week |
| 1 | 7716856 | 11 | 202407 | 202407 |
| 2 | 7716856 | 12 | 202407 | 202407 |
| 3 | 7713693 | 40 | 202409 | 202409 |
| 4 | 7716442 | 19 | 202410 | 202410 |
| 5 | 7714624 | 62 | 202410 | 202410 |
| 6 | 7714624 | 174 | 202410 | 202410 |
| 7 | 7715866 | 205 | 202410 | 202410 |
| 8 | 7715867 | 153 | 202410 | 202411 |
| 9 | 7711139 | 20 | 202410 | 202410 |
| 10 | 7711139 | 30 | 202411 | 202411 |
On record 8, you can see the job was too large for the remaining capacity and scheduled for the next week, while the smaller job on row 9 was able to fit into the remaining week 10 capacity.
| Scheduling Week | Capacity (hours) | Adjusted Capacity |
| 202405 | 500 | 500 |
| 202406 | 500 | 500 |
| 202407 | 500 | 477 |
| 202408 | 500 | 500 |
| 202409 | 500 | 460 |
| 202410 | 500 | 20 |
| 202411 | 500 | 317 |
Any help or direction on this is greatly appreciated.
Thank you!
