Report Eng SkillCount AssCompCount AssPendingCount
DECLARE @centerId INT = 1
SELECT u.UserId EngUserId
,u.UserName EngName
,u.Email EngEmail
,u.SecondaryRole
,u.Region
,u.FacilityDesc
,u.ManagerName
,u.ManagerName2
,u.ManagerName3
,count(DISTINCT uccps.Id) AS TotalSkillAssigned
,sum(CASE
WHEN a.AssessmentStatusId = 5
THEN 1
ELSE 0
END) AS AssessmentCompletedCount
,sum(CASE
WHEN a.AssessmentStatusId <> 5
THEN 1
ELSE 0
END) AS AssessmentPendingCount
FROM dbo.[User] u
INNER JOIN dbo.UserXCenter uc ON u.UserId = uc.UserId
AND uc.CenterId = @centerId
LEFT JOIN dbo.UserCenterXCenterPguSkill uccps ON uc.UserXCenterId = uccps.UserXCenterId
AND uccps.IsActive = 1
LEFT JOIN dbo.CenterXPGUXSkill cps ON uccps.CenterXPGUXSkillId = cps.CenterXPGUXSkillId
LEFT JOIN dbo.CenterXPGU cp ON cps.CenterXPGUId = cp.CenterXPGUId
LEFT JOIN dbo.PGU p ON cp.PGUId = p.PGUId
LEFT JOIN dbo.Skill s ON cps.SkillId = s.SkillId
LEFT JOIN dbo.Assessment a ON uccps.Id = a.UserCenterXCenterPguSkillId
AND a.IsActive = 1
AND a.WaveId IN (
77
,69
,68
,66
,65
,60
,59
)
LEFT JOIN dbo.Wave w ON a.WaveId = w.Id
WHERE u.IsActive = 1
AND u.FacilityDesc = 'CSC BGL'
--AND u.InternalManagerId = 2733
GROUP BY u.UserId
,u.UserName
,u.Email
,u.SecondaryRole
,u.Region
,u.FacilityDesc
,u.ManagerName
,u.ManagerName2
,u.ManagerName3
ORDER BY u.UserId
,u.UserName
,u.SecondaryRole