I have Project and time slot in seperate tables.
I want to schedule time slot for group of projects based on certain condition.
If 1. Project review is 1st review schedule 45 mins
2. Project review is not 1st review schedule 15 mins
Schedule Time (9:00 am to 11:300 am) for every monday.
Project Data:
Project ID, Review, Date, Region, Time Slot, Status
A, 1st Review, 5/13/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
A, 2st Review, 5/13/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
A, 3st Review, 5/20/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
A, 4st Review, 5/20/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
B, 1st Review, 5/13/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
B, 2st Review, 5/20/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
C, 1st Review, 5/13/2024, AP,<Fill the Time slot> , <USED- if alloted, Otherwise BLANK>,
C, 2nd Review, 5/13/2024, AP, <Fill the Time slot> , <USED- if alloted, Otherwise BLANK>
Timeslot Table
| 5/13/2024 |
09:00 - 09:15 |
| 5/13/2024 |
09:15 - 09:30 |
| 5/13/2024 |
09:30 - 09:45 |
| 5/13/2024 |
09:45 - 10:00 |
| 5/13/2024 |
10:00 - 10:15 |
| 5/13/2024 |
10:15 - 10:30 |
| 5/13/2024 |
10:30 - 10:45 |
| 5/13/2024 |
10:45 - 11:00 |
| 5/13/2024 |
11:00 - 11:15 |
| 5/13/2024 |
11:15 - 11:30 |
| 5/20/2024 |
09:00 - 09:15 |
| 5/20/2024 |
09:15 - 09:30 |
| 5/20/2024 |
09:30 - 09:45 |
| 5/20/2024 |
09:45 - 10:00 |
| 5/20/2024 |
10:00 - 10:15 |
| 5/20/2024 |
10:15 - 10:30 |
| 5/20/2024 |
10:30 - 10:45 |
| 5/20/2024 |
10:45 - 11:00 |
| 5/20/2024 |
11:00 - 11:15 |
| 5/20/2024 |
11:15 - 11:30 |
Output:
Project ID, Review, Date, Region, Time Slot, Status
A, 1st Review, 5/13/2024, AP,9:00 - 9:45 , USED,
A, 2nd Review, 5/13/2024, AP,9:45 - 10:00 , USED,
B, 1st Review, 5/13/2024, AP,10:00 - 10:45, USED,
C, 1st Review, 5/13/2024, AP,10:45 - 11:30, USED,
C, 2nd Review, 5/13/2024, AP, No Slot available, Not Confirmed,
