Chandrashekar
8/4/2016 - 12:05 PM

Update-Assessment and Skill Un Assignment for engineers under given manager and skill

Update-Assessment and Skill Un Assignment for engineers under given manager and skill

Assessment and Skill Un Assignment for engineers under given manager and skill.sql
content_copyfile_download

--In Activate all assessments of engineers under managerUserId 3208 for CenterPguSkillId 9
SELECT *
FROM dbo.Assessment a
INNER JOIN dbo.UserCenterXCenterPguSkill ucxps ON ucxps.Id = a.UserCenterXCenterPguSkillId
INNER JOIN dbo.UserXCenter ux ON ux.UserXCenterId = ucxps.UserXCenterId
INNER JOIN dbo.[User] u ON u.UserId = ux.UserId
WHERE u.InternalManagerId = 3208
      AND ucxps.CenterXPGUXSkillId = 9

--
UPDATE a
SET a.IsActive = 0
FROM dbo.Assessment a
INNER JOIN dbo.UserCenterXCenterPguSkill ucxps ON ucxps.Id = a.UserCenterXCenterPguSkillId
INNER JOIN dbo.UserXCenter ux ON ux.UserXCenterId = ucxps.UserXCenterId
INNER JOIN dbo.[User] u ON u.UserId = ux.UserId
WHERE u.InternalManagerId = 3208
      AND ucxps.CenterXPGUXSkillId = 9

--Un Assigning Skill assigned under ManagerUserId : 3208 and CPS:9
SELECT *
FROM dbo.UserCenterXCenterPguSkill ucxps
INNER JOIN dbo.UserXCenter ux ON ux.UserXCenterId = ucxps.UserXCenterId
INNER JOIN dbo.[User] u ON u.UserId = ux.UserId
WHERE u.InternalManagerId = 3208
      AND ucxps.CenterXPGUXSkillId = 9

UPDATE ucxps
SET ucxps.IsActive = 0
FROM dbo.UserCenterXCenterPguSkill ucxps
INNER JOIN dbo.UserXCenter ux ON ux.UserXCenterId = ucxps.UserXCenterId
INNER JOIN dbo.[User] u ON u.UserId = ux.UserId
WHERE u.InternalManagerId = 3208
      AND ucxps.CenterXPGUXSkillId = 9