Missing ethnicity for students

[Ghislain Hachey]

Seems latest fix applied for staff data does not work with students.

‌

[Brian Lewis]

Ethnicity as recorded on Student_ record is normalised using the table lkpEthinicity - ie the value on the workbook is look up in DESCRIPTION, and the matching CODE is written to the table.

‌

It seems that lkpEthnicity may not ahve been set up when workbooks were last loaded - so there was no CODe to record.

Reprocessing (using the student enrolment Xml) recreates the missing Ethnicity field. Then the census Rollover carries the value without problem.

No code or Sql changes were need to resolve this.

‌

[Brian Lewis]

Data modifications made to fix missing data.

[Ghislain Hachey]

@Brian Lewis What data modification was made exactly? I’m pretty sure that lkpEthnicity has had data for quite some time.

[Brian Lewis]

UPDATE STudent_
SET stuEthnicity = E.codeCode
from Student_
INNER JOIN StudentEnrolment_ 
ON Student_.stuID = StudentEnrolment_.stuID
AND stueYear = 2022
LEFT JOIN lkpEthnicity E
    ON  stueData.value('(//row/@Ethnicity)[1]','nvarchar(100)') = codeDescription
WHERE stuEthnicity is null

@Ghislain Hachey This repairs the missing ethnicity from the workbook value.

Code change to load ethnicity was in origin/issue597 loaded 16 May 2019. (prior to rename of these Stored Procs)