Data warehouse DistrictTeacherCount

[Ghislain Hachey]

Seems none of the qualification and certification are in there.

[Ghislain Hachey]

[Brian Lewis]

The data model allows for the possibility that the standards of qualification/certification may change from year to year. e.g. It is possible that standards may be raised, so that a qualification that makes you qualified in 2017 does not make you qualified in 2018.

This adds another layer of indirection in the calculation of certified and qualified status. This information is held in the table surveyYearTeacherQual:

The problem you are seeing is because there is no records in this table for year 2019. (You will see there is cert/qual data for earlier years).

In legacy Pineapples, this data is copied across from year to year when a new Survey Year is opened. This is handled by creating the new survey year using the SP pSurveyWriteX.CreateSurveyYear.

This is adapted from that stored proc:

‌

begin transaction
declare @SurveyYear int = 2019



    declare @MaxYear int
    Select @MaxYear = svyYear
    from Survey
    WHERE svyYear <> @SurveyYear

   -- copy all the school type definitions to the new year from the last
   INSERT INTO [SurveyYearSchoolTypes]
           ([svyYear]
           ,[stCode]
           ,[ytForm]
           ,[ytTeacherForm]
           ,[ytAgeMin]
           ,[ytAgeMax]
           ,[ytConfig])
    SELECT
            @SurveyYear
           ,[stCode]
           ,[ytForm]
           ,[ytTeacherForm]
           ,[ytAgeMin]
           ,[ytAgeMax]
           ,[ytConfig]
    FROM SurveyYearSchoolTypes
    WHERE svyYear = @MaxYear

 -- copy over teacher qualifations - always been a headache
    INSERT INTO [SurveyYearTeacherQual]
           ([svyYear]
           ,[ytqSector]
           ,[ytqQual]
           ,[ytqQualified]
           ,[ytqCertified])
     SELECT
            @SurveyYear
          ,[ytqSector]
          ,[ytqQual]
          ,[ytqQualified]
          ,[ytqCertified]
      FROM [SurveyYearTeacherQual]
    WHERE svyYear = @MaxYear
    select @maxYear

    Select * from surveyYearTEacherqual
    where svyYear = @surveyYear
commit

This code has been run now in live FEDEMIS. Run a warehouse build to regenerate Teacher counts.

[Brian Lewis]

data configuration - see comments. Rerun warehouse rebuild

[Ghislain Hachey]

@Brian Lewis why were there no teacher qualifications? We have highest qualifications in survey year 2019. We just don’t have highest ed qualification (highest certification).