Mismatch between total teachers reported in Indicators dashboard and Teachers Dashboard

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)