View warehouse.TeacherPerformance issue with salary data

[Ghislain Hachey]

No description provided.

[Ghislain Hachey]

Issue fixed but the problem is not yet addressed. It will be as part of issue #1235. Meanwhile, the salary data can be “brought in” the desired location by getting it from the XML data row as follows.

‌

-- Fix missing salary in TeacherSurvey tchSalary.

BEGIN TRANSACTION

SELECT SS.svyYear
, SUM(TeacherSurvey.tchSalary)
FROM
TeacherSurvey
INNER JOIN 
(SELECT ssID
, tID
, TS.tchSalary
, r.value('@Annual_Salary','nvarchar(50)') XML_Salary
FROM TeacherSurvey TS
OUTER APPLY tcheData.nodes('row') R(r)
) SALARY
    ON TeacherSurvey.ssID = SALARY.ssID
    AND TeacherSurvey.tID = SALARY.tID
INNER JOIN SchoolSurvey SS ON TeacherSurvey.ssID = SS.ssID
GROUP BY SS.svyYear

SELECT TeacherSurvey.ssID
, TeacherSurvey.tID
, TeacherSurvey.tchSalary
, SALARY.*
FROM
TeacherSurvey
INNER JOIN 
(SELECT ssID
, tID
, TS.tchSalary
, r.value('@Annual_Salary','nvarchar(50)') XML_Salary
FROM TeacherSurvey TS
OUTER APPLY tcheData.nodes('row') R(r)
) SALARY
    ON TeacherSurvey.ssID = SALARY.ssID
    AND TeacherSurvey.tID = SALARY.tID

UPDATE TeacherSurvey
SET tchSalary = SALARY.XML_Salary
FROM
TeacherSurvey
INNER JOIN 
(SELECT ssID
, tID
, TS.tchSalary
, r.value('@Annual_Salary','nvarchar(50)') XML_Salary
FROM TeacherSurvey TS
OUTER APPLY tcheData.nodes('row') R(r)
) SALARY
    ON TeacherSurvey.ssID = SALARY.ssID
    AND TeacherSurvey.tID = SALARY.tID

SELECT SS.svyYear
, SUM(TeacherSurvey.tchSalary)
FROM
TeacherSurvey
INNER JOIN 
(SELECT ssID
, tID
, TS.tchSalary
, r.value('@Annual_Salary','nvarchar(50)') XML_Salary
FROM TeacherSurvey TS
OUTER APPLY tcheData.nodes('row') R(r)
) SALARY
    ON TeacherSurvey.ssID = SALARY.ssID
    AND TeacherSurvey.tID = SALARY.tID
INNER JOIN SchoolSurvey SS ON TeacherSurvey.ssID = SS.ssID
GROUP BY SS.svyYear

SELECT TeacherSurvey.ssID
, TeacherSurvey.tID
, TeacherSurvey.tchSalary
, SALARY.*
FROM
TeacherSurvey
INNER JOIN 
(SELECT ssID
, tID
, TS.tchSalary
, r.value('@Annual_Salary','nvarchar(50)') XML_Salary
FROM TeacherSurvey TS
OUTER APPLY tcheData.nodes('row') R(r)
) SALARY
    ON TeacherSurvey.ssID = SALARY.ssID
    AND TeacherSurvey.tID = SALARY.tID

ROLLBACK

‌