Important Things To Remember When Working on Employment Group Restructure Tasks

When working on an employment group re-structure task, it is important to remember to delete any existing BCI scores for the employment groups belonging to the company survey you are working with.

These scores are kept in the Dat_Survey_Employment_Group_Accreditation_Rating and can be deleted with the following query:

DELETE dsegar
FROM Dat_Employment_Groups deg
LEFT JOIN Dat_Survey_Employment_Group_Accreditation_Rating dsegar ON dsegar.EmploymentGroupID = deg.EmploymentGroupID
WHERE deg.CompanySurveyID = @CompanySurveyID

There is already a trigger on the Dat_Employment_Groups table which does this when parent groups have changed, or groups being added or removed, so this extra delete statement only needs to be called if the employment groups themselves have not been changed, only the employees' employment group answers.

Deleting the records from the Dat_Survey_Employment_Group_Accreditation_Rating table will force WIT or the Data Packs to re-generate the records as and when they are next needed, so adding this delete statement to the end of your employment group re-structure schema change is all that is required to ensure that the employment group BCI scores are always up-to-date.