Forum Discussion
Calculate Projects that go back and forth between stages
Posting on this Desktop as well to hopefully get an answer.
I am trying to calculate the time duration a project stays in any given stage. The problem is, that a project can jump back and forth between stages and departments depending of the scope of the project.
How would I calculate total time in each Stage AND how many times does a project enter/leave a stage? I will need to know the duration of time within each Stage instance.
Any help would be greatly appreciated!
Below is a table table of 1 project. Currently, all projects are housed in one large status table. Everytime an action occurs, a new Status_ID is created and the progress is added.
| Status_Id | ProjectId | StageId | Created | Note | Parent Status_Id | Stage_Name |
| 3814 | 1 | 1031 | 2/10/2022 | 2.10.22 Saved | 3811 | Tech Review |
| 3835 | 1 | 1031 | 2/10/2022 | 2.10.22. Saved | 3829 | Tech Review |
| 3829 | 1 | 1031 | 2/10/2022 | 2.10.22 update released drawings. | 3814 | Tech Review |
| 3811 | 1 | 1031 | 2/10/2022 | 2.10.22 update released drawings | 3727 | Tech Review |
| 3837 | 1 | 1047 | 2/10/2022 | 2.10.22 Please release to production | 3835 | Production |
| 3727 | 1 | 1031 | 2/9/2022 | 2.10.22 update released drawings | 3688 | Tech Review |
| 3688 | 1 | 1031 | 2/8/2022 | 2.8.22 update released drawings | 3675 | Tech Review |
| 3675 | 1 | 1031 | 2/8/2022 | 2.8.22. Proofsent back | 3672 | Tech Review |
| 3672 | 1 | 1031 | 2/8/2022 | 2.8.22 updated proof | 3540 | Tech Review |
| 3703 | 1 | 1047 | 2/8/2022 | 2.8.22: Please release to production | 0 | Production |
| 3540 | 1 | 1031 | 2/4/2022 | 2.4.22. Saved | 3484 | Tech Review |
| 3482 | 1 | 1031 | 2/3/2022 | 2.3.22 Saved | 3481 | Tech Review |
| 3484 | 1 | 1031 | 2/3/2022 | 2.3.22 saved | 3482 | Tech Review |
| 3481 | 1 | 1023 | 2/3/2022 | 2.3.22 2nd proof | 3396 | Review Proof |
| 3396 | 1 | 1031 | 2/2/2022 | 2.2.22 Sent proof back | 3391 | Tech Review |
| 3391 | 1 | 1031 | 2/2/2022 | 2.2.22 update 2nd proof | 3227 | Tech Review |
| 3222 | 1 | 1022 | 1/28/2022 | 1.28.22: Please see changes | 0 | Graphics |
| 3227 | 1 | 1031 | 1/28/2022 | 1.27.22 update 1st proof | 3222 | Tech Review |
| 3151 | 1 | 1031 | 1/27/2022 | 1.27.22 update 1st proof | 3128 | Tech Review |
| 3177 | 1 | 1031 | 1/27/2022 | 1.27.22 update 1st proof | 3151 | Tech Review |
| 3128 | 1 | 1031 | 1/27/2022 | 1.27.22 update 1st proof | 2871 | Tech Review |
| 3187 | 1 | 1087 | 1/27/2022 | 1.27.22 1st proof | 3184 | Review Proof |
| 3184 | 1 | 1031 | 1/27/2022 | 1.27.22 Internal Proofing | 3177 | Tech Review |
| 2871 | 1 | 1031 | 1/21/2022 | 1.26.22 internal proofing | 2294 | Tech Review |
| 2294 | 1 | 1021 | 1/7/2022 | 1.20.22: Please note | 0 | Graphics |
| 879 | 1 | 1128 | 12/1/2021 | 0 | Follow up Estimators | |
| 773 | 1 | 1124 | 11/29/2021 | Please revise the quote | 0 | Estimating |
| 278 | 1 | 1124 | 11/10/2021 | compile a quote | 0 | Estimating |
3 Replies
- lbendlin
Super User
Anonymous
Why do stage names have multiple stage IDs ?
What's the significance/meaning of the Parent Status ID? It doesn't seem to follow any discernable logic. What's the case of it being 0?
I will need to know the duration of time within each Stage instance.You don't have time level granularity. Did you mean days? Which ones? Calendar days? Business days?
A stage name may have a diffent ID becuase it has a different opperation in the process. The name is just a high level name applied to the step.
I would like to Know total calendar days. I am trying to get to the level that says:
Tech review had to be started 6 seperate times. Each instance had an average length of XXX calendar days.
- lbendlin
I can't see how you get to that based on the sample data. Please provide sanitized sample data that fully covers your issue. Please show the expected outcome based on the sample data you provided.
