- edited description
Mismatch between total teachers reported in Indicators dashboard and Teachers Dashboard
Issue #1372
resolved
This is cause by teachers without a gender going “missing” in the Teachers dashboard.
The Teacher Dashboard relies on the query below:
SELECT [SurveyYear]
,[AgeGroup]
,TCS.[DistrictCode]
,DSS.[Island Code] [IslandCode]
,DSS.[Region Code] [RegionCode]
,DSS.[AuthorityCode]
,DSS.[AuthorityGovtCode] [AuthorityGovtCode]
,DSS.[SchoolTypeCode]
,[Sector]
,[ISCEDSubClass] [ISCEDSubClassCode]
, sum(case GenderCode when 'M' then NumTeachers else null end) NumTeachersM
, sum(case GenderCode when 'F' then NumTeachers else null end) NumTeachersF
, sum(case GenderCode when 'M' then Certified else null end) CertifiedM
, sum(case GenderCode when 'F' then Certified else null end) CertifiedF
, sum(case GenderCode when 'M' then Qualified else null end) QualifiedM
, sum(case GenderCode when 'F' then Qualified else null end) QualifiedF
, sum(case GenderCode when 'M' then CertQual else null end) CertQualM
, sum(case GenderCode when 'F' then CertQual else null end) CertQualF
FROM [warehouse].[TeacherCountSchool] TCS
INNER JOIN [warehouse].[dimensionSchoolSurvey] DSS ON TCS.SchNo = DSS.[School No] AND TCS.SurveyYear = DSS.[Survey Data Year]
GROUP BY [SurveyYear]
,[AgeGroup]
,TCS.[DistrictCode]
,DSS.[Island Code]
,DSS.[Region Code]
,DSS.[AuthorityCode]
,DSS.[AuthorityGovtCode]
,DSS.[SchoolTypeCode]
,[Sector]
,[ISCEDSubClass]
The query can be slightly modified like so to include unknown genders.
SELECT
[SurveyYear],
[AgeGroup],
TCS.[DistrictCode],
DSS.[Island Code] AS [IslandCode],
DSS.[Region Code] AS [RegionCode],
DSS.[AuthorityCode],
DSS.[AuthorityGovtCode] AS [AuthorityGovtCode],
DSS.[SchoolTypeCode],
[Sector],
[ISCEDSubClass] AS [ISCEDSubClassCode],
SUM(CASE WHEN GenderCode = 'M' THEN NumTeachers ELSE NULL END) AS NumTeachersM,
SUM(CASE WHEN GenderCode = 'F' THEN NumTeachers ELSE NULL END) AS NumTeachersF,
SUM(CASE WHEN GenderCode NOT IN ('M', 'F') OR GenderCode IS NULL THEN NumTeachers ELSE NULL END) AS NumTeachersNA,
SUM(CASE WHEN GenderCode = 'M' THEN Certified ELSE NULL END) AS CertifiedM,
SUM(CASE WHEN GenderCode = 'F' THEN Certified ELSE NULL END) AS CertifiedF,
SUM(CASE WHEN GenderCode NOT IN ('M', 'F') OR GenderCode IS NULL THEN Certified ELSE NULL END) AS CertifiedNA,
SUM(CASE WHEN GenderCode = 'M' THEN Qualified ELSE NULL END) AS QualifiedM,
SUM(CASE WHEN GenderCode = 'F' THEN Qualified ELSE NULL END) AS QualifiedF,
SUM(CASE WHEN GenderCode NOT IN ('M', 'F') OR GenderCode IS NULL THEN Qualified ELSE NULL END) AS QualifiedNA,
SUM(CASE WHEN GenderCode = 'M' THEN CertQual ELSE NULL END) AS CertQualM,
SUM(CASE WHEN GenderCode = 'F' THEN CertQual ELSE NULL END) AS CertQualF,
SUM(CASE WHEN GenderCode NOT IN ('M', 'F') OR GenderCode IS NULL THEN CertQual ELSE NULL END) AS CertQualNA
FROM
[warehouse].[TeacherCountSchool] TCS
INNER JOIN
[warehouse].[dimensionSchoolSurvey] DSS
ON
TCS.SchNo = DSS.[School No] AND TCS.SurveyYear = DSS.[Survey Data Year]
GROUP BY
[SurveyYear],
[AgeGroup],
TCS.[DistrictCode],
DSS.[Island Code],
DSS.[Region Code],
DSS.[AuthorityCode],
DSS.[AuthorityGovtCode],
DSS.[SchoolTypeCode],
[Sector],
[ISCEDSubClass];
Comments (2)
-
reporter -
reporter - changed status to resolved
feat: support for missing gender in the Teachers Dashboard
Now total will line up with Indicators dashboard and raw data even in the absense of teacher gender data.
Resolves
#1372→ <<cset 74d4a671b94c>>
- Log in to comment