Renewed certification of teachers not considered

The stored procedure [warehouse].[buildTeacherLocation] is responsible for building the table [warehouse].[TeacherQual] among other things. The table [warehouse].[TeacherQual] holds all teacher’s qualification from both surveys and records done manually through the web UI (which go in [dbo].[TeacherTraining]).

It is done through this section of the procedure:

INSERT INTO Warehouse.TeacherQual
        ( tID, yr, tchQual)
        Select tID
        , min(yr) yr
        , qual
        FROM (
            Select tID
                , svyYear yr
                , tchQual Qual
                FROM TEacherSurvey TS
                INNER JOIN SchoolSurvey SS
                    ON TS.ssID = SS.ssID
                WHERE tchQual is not null

            UNION
                Select tID
                , svyYear yr
                , tchEdQual
                FROM TEacherSurvey TS
                INNER JOIN SchoolSurvey SS
                    ON TS.ssID = SS.ssID
                WHERE tchEdQual is not null
            UNION
                Select tID
                , coalesce(trYear, @StartFromYear, 0) yr
                , trQual
                FROM TeacherTraining
                ) U
        WHERE (yr >= @StartFromYear or @StartFromYEar is null)
        GROUP BY tID, qual

        print 'warehouse.teacherQual inserts - rows:' + convert(nvarchar(10), @@rowcount)

But since the introduction of the concept of duration for the validity of teacher certifications the above does not take into consideration the renewed (more recently acquired) certifications. Only the oldest one (through the min aggregation). For example, a teacher with a certification record in 2018 and another in 2022 would be shown as certified only in 2018, 2019, 2020, and 2021 in TeacherLocation, TeacherCountSchool and all related tables/views when clearly they should also be shown as certified in 2022, 2023, 2024, 2025 also.

A quick fix could be to add something like the following to the above result set.

        -- A quick fix for adding more recent certifications (or anything with a non permanent duration) 
        -- that are otherwise lost by the above MIN() aggregation. 
        -- Also note that the trYear is not always a good measure of the first year the teacher should be considered as certified *in that school year*
        -- A simple solution would be to introduce some cutoff date after which teachers would not have been considered certified in that
        -- school year but only the next. For example, take a teacher that was certified with an trEffectiveDate of 8 August 2022 and a
        -- trExpiryDate of 8 August 2026. The trYear will be set by the user to 2022 but the teacher was not really considered certified 
        -- in school year 2022 (i.e. 2021-22). It would be more correct to say the teacher was certified in school years 2023, 2024, 2025, 2026.
        -- If the certification record came from the survey (above) then there is no need for this distinction but with carefully entered certification
        -- record I think it is fairly easy and important to come up with something a bit more refined as below.

        UNION

        Select tID
            , coalesce(common.SchoolYearInt(trEffectiveDate, common.sysParam('TEACHERCERT_CUTOFFDATE')), trYear, null, 0) yr
            , trQual
        FROM TeacherTraining TT
        LEFT OUTER JOIN lkpTeacherQual LTQ ON TT.trQual = LTQ.codeCode
        WHERE (codeDurationYears IS NOT NULL AND codeDurationYears != 0) -- Only consider qualifications with a duration 

        print 'warehouse.teacherQual inserts - rows:' + convert(nvarchar(10), @@rowcount)

‌

Comments (3)