I have access to a database which has details of projects submitted by users which are assigned to different vendors.
I am trying to calculate average time taken by vendors for projects (Avg time for Submit to Quote Approved, Quote Approved to Completed etc)
There are two types of projects, Ad hoc projects and Milestone projects and I need to build separate views for them.
Adhoc projects are individual standalone projects. Most of our projects are Adhoc.
For milestone projects, there will be a parent project and one or more sub projects (child projects)
As shown in the sample table below, MM1234 and SP1234 are milestone projects. These projects have child projects which have their respective Parent Request No updated.
Is there a way I can filter for only these milestone projects ( MM1234 and SP1234) so that I can calculate their average - The child projects of these projects should not be included in the average calculation
Also attached the sample data for reference
|Project id||Proj Title||Client||Submit Date||Quote Approved|
|Completed Date||Status||Vendor||Parent Request|
|DD1234||Slides creation 2||xy||1/3/2018||1/3/2018||1/7/2018||Completed||North|
|MM1234||Media monitoring for|
|MM1235||Media monitoring for|
|MM1236||Media monitoring for 2018 - Feb||BC||1/1/2018||1/1/2018||3/30/2018||Completed||West||MM1234|
|MM1237||Media monitoring for|
2018 - Mar
|MM1238||Media monitoring for|
2018 - Apr
|TR1235||Translation job 2||BC||4/10/2018||4/10/2018||Quote Approved||East|
|SP1237||SP Testing||amc||2/15/2018||2/20/2018||Quote Approved||South||SP1234|