如果我正确理解当雇员提交调查时CompleteSurvey充满EmployeeID,则此查询将检索您所需的信息。您可以检查实时测试@Sql Fiddle。
select Departments.DepatName,
count (Employees.EmployeeID) Employees,
count (CompleteSurvey.ID) Finished,
cast (count (CompleteSurvey.ID) as float) / count (Employees.EmployeeID)
PercentageFinished
from Departments
inner join Employees
on Departments.OrgCode = Employees.OrgCode
left join CompleteSurvey
on Employees.EmployeeID = CompleteSurvey.ParticipantID
group by Departments.DepatName
如果需要所有部门,请使用以下查询:
select Departments.DepatName,
count (Employees.EmployeeID) Employees,
count (CompleteSurvey.ID) Finished,
case when count (Employees.EmployeeID) <> 0
then cast (count (CompleteSurvey.ID) as float)
/ count (Employees.EmployeeID)
else 0
end PercentageFinished
from Departments
left join Employees
on Departments.OrgCode = Employees.OrgCode
left join CompleteSurvey
on Employees.EmployeeID = CompleteSurvey.ParticipantID
group by Departments.DepatName