I am trying to calculate an overall percentage complete from a table and return the Overall Percentage back as a single column in an Select (as I want to use the percentage in another select; this should be a sub-query)
I have the calculations working to get TotalEffort and CompletedEffort - see below - but can someone tell me how to then calculate the percentage of these two columns?
For example, the select below would return 510 for both columns so the Overall Percentage Competed would be 100
Sum(P.Duration) as TotalEffort,
Sum(((Cast(P.Duration as Decimal) / 100) * Cast(P.PercentComplete as Decimal))) as CompletedEffort
[Portfolio Register] PR INNER JOIN Workstreams W ON PR.[Project Reference] = W.[Project Reference]
INNER JOIN Plans P ON W.ID = P.WorkstreamID
PR.[Project Reference] = 'PR00277 Study' and Duration > 0