warehouse.TeacherQual does not take into consideration expiry of education qualification (certification)

[Ghislain Hachey]

When the store proc [warehouse].[buildTeacherLocation] builds [warehouse].[TeacherQual] it does not seems like it is taking into consideration the possible expiry of certificates. Should a teacher with a certification reported in 2017 (good for three years with clear trEffectiveDate and trExpirationDate set) still have a record in this warehouse table?

‌

I also fail to see how in other warehouse tables that contain the Qualified and Certified boolean that the certified takes into considerations possible expiry of certificates.

[Brian Lewis]

Added codeDurationYears to lkpTeacherQual.

This field, if not null, is the number of years that certification/qualification associated with that qualification will apply for.

‌

To create a simple test:

‌

‌

ANTC1 expires in 1 year. This means it will only bestow Certification for 1 year, being the year that the certificate was obtained.

‌

teacher 4587 got ANTC1 in 2014. So they will be Certified in 2014 but not after that:

‌

teacher 6627 got a ANTC2 in 2018. this makes them Certified for 2018 and 2019, but not after that:

‌

‌

‌

Remove the duration from ANTC1 in lkpTeacherQual. Then 4587 is Certified from the year they got the certificate and all years after;

‌

‌

[Brian Lewis]

I think warehouse.TeacherQual should contain all the qualifications a teacher has earned. Even without considerations of expiry, it does not directly relate to whether those Qualifations make the teacher Qualified or Certified.

The calculation of Qualified Certified status by teacher by year is on warehouse.TecherLocation, and is implemented in warehouse.buildTeacherLocation. This is where the change is to support taking action on DurationYears

warehouse.BuildTeacherLocation stored proc will be the main part of the commit resolving this issue.

‌

Excerpt:

‌

INNER JOIN warehouse.TeacherQual TQ
    ON  LOC.tID = TQ.tID
    AND Q.ytqQual = TQ.tchQual
    AND ( 
        TQ.yr is null -- those teachers with a qualification without a year are assumed to have been qualified way back
        OR 
            TQ.yr <= LOC.SurveyYear 
        )
        AND (isnull(LKP.codeDurationYears,0) = 0  -- Duration 0 means a permanent qualification - ie does not expire
        -- the year is within the duration of the certification period
        -- note if there is no year - TQ.yr is null -- then this expression is null so the certification 
        -- will be considered expired
        -- also not < not <= here; in other words if DurationYears = 1, the certification will only apply
        -- for the year in which the qualification was obtained
        OR LOC.surveyYear < TQ.yr + LKP.codeDurationYears
        )

‌

[Ghislain Hachey]

@Brian Lewis ok. But how does the effective date and expiry date of a certification fit in this?

[Brian Lewis]

feat(teachers): allow Certification/Qualification to expire after N years

Resolves #1199

→ <<cset a8a4f4d50285>>