Outputs:

SQL: [IDI_Community].[edu_quals_moe_school_leavers].[quals_moe_school_leavers_202603]
SAS: libname cm_quals_moe_school_leavers dsn=IDI_community_srvprd schema=edu_quals_moe_school_leavers; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Output Table

SQL: [IDI_Community].edu_quals_moe_school_leavers.quals_moe_school_leavers_YYYYMM
SAS: libname cmedqsl ODBC dsn=IDI_community_srvprd schema=edu_quals_moe_school_leavers;
How to access a code module in the Data Lab : Read here

Context

This script provides the highest secondary school achievement that an individual has attained at the time of leaving a school. The list of highest attainments includes NCEA, other NZQF qualifications, overseas exam attainments or credits achieved. The school leavers dataset is the only dataset that contains non-NZQF qualifications, e.g. International Baccalaureate and Cambridge International Curriculum exams. This table is used by MoE to compile official statistics displayed on Education Counts (https://www.educationcounts.govt.nz/home).

A student may have multiple school leaving records, hence the expected business key for this spell dataset is one row per student, per leaving year and per provider. However, there are a small number of duplicates found on this business key, with different values for student type and highest attainment. It is unclear why a student willmight have multiple leaving records from the same provider and for the same year with different student types and/or highest attainment. There are also cases where exact duplicates occur except for the snz_unique_id column. These codes treat all these as data anomalies and ignores the existence of these duplicates while defining the business key.

Key Business Rules

1. For non-NCEA level qualifications, this SQL attempts to create an equivalent pseudo-NZQF level; this output is in the “level_sl” column. The logic of this equivalence is obtained from Ministry of Education. For example- if a student attained a non-NCEA qualification of “International Baccalaureate Year 13”, it is considered equivalent to “NCEA Level 3 with Excellence” (Code: 62), and both are assigned an pseudo-NZQF level of “3”, on par with NZQF level 3. The highest attainment codes used in this mapping can be obtained from the MoE data dictionary appendices on the IDI Wiki.
2. Such non-NCEA qualifications have been flagged with the “nonNQF_level_flag” column.
3. In cases where no formal qualifications are achieved at the time of leaving school, these are listed as an NZQF level “0”.
4. The spell dates are both pseudo-dates. MoE School Leavers data only supplies the calendar year in which the student left a particular school and the highest qualification that was attained by the student during their time at that school. Here, we are left toWe have assumed that the “latest” date by which that qualification was obtained, is the last day of the school-leaving year. The school qualifications are assumed to be valid for a lifetime, and hence the qualification expiry date is conceptually “infinite” and represented as 31-Dec-9999. The spell is effectively the period between this school leaving date and qualification expiry date.

Inputs

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that havewith the same name.

Version Log

List of key contacts from the Community of Interest

Code

/* Set dynamic variables */
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
:setvar idimetaversion "{idimetaversion}"

/* Establish database for writing views (GenData version only) */
USE $(targetdb);
GO

DROP VIEW IF EXISTS [$(targetschema)].[$(projprefix)_moe_schoolleaver_quals];
GO
CREATE VIEW [$(targetschema)].[$(projprefix)_moe_schoolleaver_quals] AS

SELECT a.snz_uid
    , CAST('SCH_LEAVE' AS VARCHAR(50)) AS data_source
    , a.moe_sl_highest_attain_code AS highest_attainment_code
	/* ANGUS NEW METADATA REFERENCE */
	/*,b.Classification as highest_attainment*/
    , b.HighestAttainmentDescription AS highest_attainment
    , a.moe_sl_ue_entrance_code AS ue_entrance_code
    , a.moe_sl_provider_code AS provider_code
    , DATEFROMPARTS(a.moe_sl_leaver_year, 12, 31) AS school_leaving_date
    , DATEFROMPARTS(9999, 12, 31) AS qual_expiry_date
    , CASE
		WHEN a.moe_sl_highest_attain_code IN (43,40)							THEN  4
		WHEN a.moe_sl_highest_attain_code IN (37,36,35,34,33,62,72,82,92)		THEN  3
		WHEN a.moe_sl_highest_attain_code IN (56,27,26,25,24,4,61,71,81,91)		THEN  2
		WHEN a.moe_sl_highest_attain_code IN (55,17,16,15,14,13,60,70,80,90)	THEN  1
		ELSE 0 
	END AS equiv_nqf_level
    , moe_sl_last_day_attend AS last_attendance_date
    , CASE 
		WHEN a.moe_sl_highest_attain_code IN (62,72,82,92,61,71,81,91,60,70,80,90) THEN 1 
        ELSE 0 
	END AS nonNQF_flag /* Non-NZQF qualifications flag*/
FROM [$(idicleanversion)].[moe_clean].[student_leavers] a
/* ANGUS NEW METEDATA REFERENCE */
LEFT JOIN [$(idimetaversion)].[moe_school].[highest_attainment_code] b
ON(
    a.moe_sl_highest_attain_code = b.HighestAttainmentCode
)
/* left join [IDI_Metadata].[clean_read_CLASSIFICATIONS].[moe_HighestAttainment] b */
/* on (a.moe_sl_highest_attain_code = b.Code) */
 ;
GO