Hi
Not sure where to start on this one.
Want to end up with the straight Table examples 1 and 2, but with the data calculated in the Script.
| Straight Table example 1 ID - A |
| | 14-01 | 14-02 | 14-03 | 14-04 | 14-05 | 14-06 | Total |
| Planned Hours | 5 | 10 | 10 | 15 | 10 | 10 | 60 |
| Actual Hours | 10 | 15 | 12 | | | | 37 |
| | | | | | | | |
| Remaining Hours | | | | 3 | 10 | 10 | 23 |
| Straight Table example 2 ID - B |
| | 14-11 | 14-12 | 15-01 | 15-02 | 15-03 | | Total |
| Planned Hours | 100 | 200 | 200 | 200 | 200 | | 900 |
| Actual Hours | | 250 | | | | | 250 |
| | | | | | | | |
| Remaining Hours | | 50 | 200 | 200 | 200 | | 650 |
Have Tasks and Resource which is combined into a ID as per ID - A and ID - B
For each ID there are Planned hours per Year-Month to complete the Task & Actual hours they have booked against the task per Year Month
What I need to calculate in the script is the Remaining Planned hours per Year – Month for each ID.
The remaining Planned hours per month cannot be greater than originally planned hours per Month.
As I start off with 5 million rows of raw data as it is stored in days in or ERP system. I have summed this up into a number of tables; the main ones I would use would be Planned Monthly Table and Actual Total Table as shown.
The Planned Monthly Table is sorted by ID and Year Month and has Row No, Total No Months and Total Planned hours.
| Planned Monthly Table |
| ID | Year Month | Planned Hours | Row No | No of Months | Total Planned Hours |
| A | 14-01 | 5 | 1 | 6 | 60 |
| A | 14-02 | 10 | 2 | 6 | 60 |
| A | 14-03 | 10 | 3 | 6 | 60 |
| A | 14-04 | 15 | 4 | 6 | 60 |
| A | 14-05 | 10 | 5 | 6 | 60 |
| A | 14-06 | 10 | 6 | 6 | 60 |
| B | 14-11 | 100 | 7 | 5 | 900 |
| B | 14-12 | 200 | 8 | 5 | 900 |
| B | 15-01 | 200 | 9 | 5 | 900 |
| B | 15-02 | 200 | 10 | 5 | 900 |
| B | 15-03 | 200 | 11 | 5 | 900 |
The Actual Total Table just has the ID and the Total Actual Hours. There are other detailed tables but didn’t think they would be wanted demonstrate this example.
| Actual Total Table |
| ID | Total Actual |
| A | 37 |
| B | 250 |
My thoughts were to calculate the delta between Total Planned and Total Actual (A = 23) (B = 650)
Then loop through each Month starting at the last month deducting the planned hours from the remaining hours, until the remaining hours are zero, then loop to the next ID. Some ID’s could be over 120 months (10 Years)
A = 23 – 10 = 13
13 – 10 = 3
3
B = 650 – 200 = 450
450 – 200 = 250
250 – 200 = 50
50
Sometimes the Actual > than planned so I would filter the table first so I don’t have to loop these then combine them later.
Hope all this makes sense I know this isn’t a 5 minute answer but need some help putting the loop together and moving form 1 ID to the next. So any help would be much appreciated.
Thanks
Mark
