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
Thanks
Select
Sum(P.Duration) as TotalEffort,
Sum(((Cast(P.Duration as Decimal) / 100) * Cast(P.PercentComplete as Decimal))) as CompletedEffort
FROM
[Portfolio Register] PR INNER JOIN Workstreams W ON PR.[Project Reference] = W.[Project Reference]
INNER JOIN Plans P ON W.ID = P.WorkstreamID
Where
PR.[Project Reference] = 'PR00277 Study' and Duration > 0