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 should only have one school leaving record, 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 (less than 5) of duplicates found on this business key, with different values for student type and highest attainment. Investigation by the Ministry has found that these are historical instances of multiple students using the same student number, and so will show the same snz_unique_id. 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 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. IDI_UserCode: 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 have the same name.

Outputs

Dependencies

{idicleanversion}.[moe_clean].[student_leavers]
[IDI_Metadata].[clean_read_CLASSIFICATIONS].[moe_HighestAttainment]

Version Log

List of key contacts from the Community of Interest

Code

/* Establish database for writing views */
/*
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
*/

-- Example
-- SQLCMD mode

:setvar targetdb "IDI_UserCode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "tmp"
:setvar idicleanversion "IDI_Clean_202306"
GO


/* Delete the database object if it already exists */
USE $(targetdb)
GO

/* Delete the database object if it already exists */
IF OBJECT_ID('[$(targetschema)].[$(projprefix)_moe_schoolleaver_quals]','V') IS NOT NULL
DROP VIEW [$(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	
	,b.Classification 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
	/* Non-NZQF qualifications flag*/
	,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
from [$(idicleanversion)].[moe_clean].[student_leavers] a
left join [IDI_Metadata].[clean_read_CLASSIFICATIONS].[moe_HighestAttainment] b on (a.moe_sl_highest_attain_code = b.Code)
;

GO
--!>