Report-Skill Assignment Zero
SELECT DISTINCT u.UserName
,u.Email
,u.SecondaryRole
,count(ucxps.Id) SkillAssignedCount
,u.ManagerName
,u.ManagerName2
,u.ManagerName3
FROM dbo.[User] u
INNER JOIN dbo.UserXCenter uc ON u.UserId = uc.UserId
AND u.CenterId = 1
LEFT JOIN dbo.UserCenterXCenterPguSkill ucxps ON uc.UserXCenterId = ucxps.UserXCenterId
AND ucxps.IsActive = 1
WHERE u.IsActive = 1
AND u.FacilityDesc = 'CSC BGL'
AND u.SecondaryRole IN (
'zz[Secondary Only] Technical Account Manager'
,'zz[Secondary Only] Technical Remote Specialist'
,'zz[Secondary Only] Technical Solution Expert'
,'zz[Secondary Only] Technical Solution Specialist'
,'zz[Secondary Only] Remote Support Specialist'
)
GROUP BY u.UserName
,u.Email
,u.SecondaryRole
,u.ManagerName
,u.ManagerName2
,u.ManagerName3
HAVING count(ucxps.Id) = 0
ORDER BY u.ManagerName,u.SecondaryRole, u.UserName
--1450
--SELECT * FROM dbo.SecondaryRoleMaster srm