Forum Discussion
Iterating and comparing rows
Hi danextian,
The logic is to rank by due date, then check if a higher prioirity task is not ontime and if so promote it up the ranks.
For ID 7 they are ranked by due date, and the higher priority task is on time, so no changes are needed.
I've played out the example process for ID 5 below.
STEP 1 - Sort by due date
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining |
| 5 | 1/08/2022 | 200 | 5 |
| 5 | 15/08/2022 | 900 | 10 |
| 5 | 20/08/2022 | 500 | 5 |
| 6 | 1/08/2022 | 100 | 5 |
| 6 | 13/08/2022 | 500 | 5 |
| 6 | 15/08/2022 | 500 | 5 |
| 7 | 5/08/2022 | 100 | 7 |
| 7 | 10/08/2022 | 100 | 4 |
| 7 | 30/08/2022 | 900 | 2 |
STEP 2 - Iterate and check whether on time, promote if needed.
EG.
Row 2, ONTIME = N, so check whether Row 2 is a higher priority than Row 1.
900 > 200 so move Row 2 up rank.
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining | ProjectedEndDate |
| |
| 5 | 1/08/2022 | 200 | 5 | 8/08/2022 | N | |
| 5 | 15/08/2022 | 900 | 10 | 22/08/2022 | N | |
| 5 | 20/08/2022 | 500 | 5 | 29/08/2022 | N | |
| 6 | 1/08/2022 | 100 | 5 | 8/08/2022 | N | |
| 6 | 13/08/2022 | 500 | 5 | 15/08/2022 | N | |
| 6 | 15/08/2022 | 500 | 5 | 22/08/2022 | Y | |
| 7 | 5/08/2022 | 100 | 7 | 10/08/2022 | N | |
| 7 | 10/08/2022 | 100 | 4 | 16/08/2022 | N | |
| 7 | 30/08/2022 | 900 | 2 | 18/08/2022 | Y |
Then EG
Row 3, ONTIME = N, so check whether Row 3 is higher priority than Row 2.
Priority 500> 200, so move Row 3 up rank
ONTIME still = No
Check whether higher priority than Row 1
Priority 500 < 900 so do not move higher.
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining | ProjectedEndDate | Ontime | |
| 5 | 15/08/2022 | 900 | 10 | 15/08/2022 | Y | |
| 5 | 1/08/2022 | 200 | 5 | 22/08/2022 |
| |
| 5 | 20/08/2022 | 500 | 5 | 29/08/2022 | N | |
| 6 | 1/08/2022 | 100 | 5 | 8/08/2022 | N | |
| 6 | 13/08/2022 | 500 | 5 | 15/08/2022 | N | |
| 6 | 15/08/2022 | 500 | 5 | 22/08/2022 | Y | |
| 7 | 5/08/2022 | 100 | 7 | 10/08/2022 | N | |
| 7 | 10/08/2022 | 100 | 4 | 16/08/2022 | N | |
| 7 | 30/08/2022 | 900 | 2 | 18/08/2022 | Y |
Finished iteration of ID5
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining | Rank | ProjectedEndDate | Ontime |
| 5 | 15/08/2022 | 900 | 10 | 1 | 15/08/2022 | Y |
| 5 | 20/08/2022 | 500 | 5 | 2 | 22/08/2022 | N |
| 5 | 1/08/2022 | 200 | 5 | 3 | 29/08/2022 | N |
| 6 | 1/08/2022 | 100 | 5 | 8/08/2022 | N | |
| 6 | 13/08/2022 | 500 | 5 | 15/08/2022 | N | |
| 6 | 15/08/2022 | 500 | 5 | 22/08/2022 | Y | |
| 7 | 5/08/2022 | 100 | 7 | 10/08/2022 | N | |
| 7 | 10/08/2022 | 100 | 4 | 16/08/2022 | N | |
| 7 | 30/08/2022 | 900 | 2 | 18/08/2022 | Y |
In step 2, how would one know whether a project is on-time or not? Also in row 2, 15/8 + 10 workdays shouldn't that be 29/8?
If ProjectedEndDate is on or before DueDate than the project is ontime
Take "TODAY" as 1 August. For example in Step 2 (screenshot below)
- Row one is 5 days and so ProjectedEndDate is 8 August
- Row two is 10 days and so ProjectedEndDate is 10 working days AFTER the end date of Row 1 (so 8 August + 10wd = 22 August)