b1nary0mega
10/7/2014 - 8:06 PM

Update an employees' group and subgroup based on existing information from another unit's profile, matching on their employee id, position a

Update an employees' group and subgroup based on existing information from another unit's profile, matching on their employee id, position and specifying what unit to match.

In this case, the below code will copy employee profile information from the 81600 profile to the 816 profile.

copyGroupInformationFromAnotherUnit.sql
content_copyfile_download
UPDATE employee_Profile p
SET p.Group_Choice_Id =
  (SELECT e.group_choice_id
  FROM Employee_Profile e
  WHERE e.employee_id     = P.Employee_Id
  AND e.unit_id           ='81600'
  AND e.Mss_Unit_Term_Dt IS NULL
  AND e.pos_code          = p.pos_code
  ) ,
  P.Sg_Choice_Id =
  (SELECT e.Sg_Choice_Id
  FROM Employee_Profile e
  WHERE e.employee_id     = P.Employee_Id
  AND e.unit_id           ='81600'
  AND e.Mss_Unit_Term_Dt IS NULL
  AND e.pos_code          = p.pos_code
  )
WHERE p.unit_id         = '816'
AND p.Mss_Unit_Term_Dt IS NULL;